Deadlock occur while creating new table to be used in partition. - Mailing list pgsql-general

Currently, I have a stored procedure(get_existing_or_create_lot), which will be called by 2 or more processes
simultaneously.

Every process will have a unique lot name. What the store procedure does it

1) Insert lot name into "lot" table. A unique lot id will be returned after insertion into "lot" table.

2) Check if unit_{id} table does exist. For example, if the returned lot id is 14, PostgreSQL will check whether
"unit_14"table does exist. If no, "CREATE TABLE unit_14..." will be executed. 


The stored procedure code is as follow :


CREATE OR REPLACE FUNCTION get_existing_or_create_lot(text)
  RETURNS TABLE(_lot_id int) AS
$BODY$DECLARE
    _param_name ALIAS FOR $1;

    _lot lot;

    unit_table_index int;
    unit_table_name text;

BEGIN
    -- Insert lot name into lot table.
    INSERT INTO lot(name) VALUES(_param_name) RETURNING  * INTO _lot;

    unit_table_index = _lot.lot_id;
    unit_table_name = 'unit_' || unit_table_index;

    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = unit_table_name) THEN
        EXECUTE 'CREATE TABLE ' || quote_ident(unit_table_name) || '
        (
          unit_id serial NOT NULL,
          fk_lot_id int NOT NULL,
          CHECK (fk_lot_id = ' || (unit_table_index) || '),
          CONSTRAINT pk_unit_' || unit_table_index || '_id PRIMARY KEY (unit_id),
          CONSTRAINT fk_lot_' || unit_table_index || '_id FOREIGN KEY (fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE
ONUPDATE NO ACTION ON DELETE CASCADE     
        ) INHERITS (unit);';

        EXECUTE 'CREATE INDEX fk_lot_' || unit_table_index || '_id_idx ON ' || quote_ident(unit_table_name) ||
'(fk_lot_id);';      
    END IF;





Unfortunately, I get the run time error ;

2010-04-26 13:28:28 MYTERROR:  deadlock detected
2010-04-26 13:28:28 MYTDETAIL:  Process 436 waits for AccessExclusiveLock on relation 46757 of database 46753; blocked
byprocess 4060.     
    Process 4060 waits for AccessExclusiveLock on relation 46757 of database 46753; blocked by process 436.
    Process 436: SELECT * FROM get_existing_or_create_lot('Testing02')
    Process 4060: SELECT * FROM get_existing_or_create_lot('Testing02')
2010-04-26 13:28:28 MYTHINT:  See server log for query details.
2010-04-26 13:28:28 MYTCONTEXT:  SQL statement "CREATE TABLE unit_16
            (
              unit_id serial NOT NULL,
              fk_lot_id int NOT NULL,
              CHECK (fk_lot_id = 16),
              CONSTRAINT pk_unit_16_id PRIMARY KEY (unit_id),
              CONSTRAINT fk_lot_16_id FOREIGN KEY (fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE ON UPDATE NO ACTION
ONDELETE CASCADE     
            ) INHERITS (unit);"
    PL/pgSQL function "get_existing_or_create_lot" line 39 at EXECUTE statement
2010-04-26 13:28:28 MYTSTATEMENT:  SELECT * FROM get_existing_or_create_lot('Testing02')



May I know why does deadlock happen? How can I avoid?

Thanks and Regards
Yan Cheng CHEOK