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

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