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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
