Deadlock occur while creating new table to be used in partition.

[Yan Cheng CHEOK <yccheok@xxxxxxxxx>]

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 ON UPDATE 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 by process 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 ON DELETE 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


      


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general