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

I post a complete code for this stored procedure, so that I won't miss out any hint which may cause this deadlock to occur.

CREATE OR REPLACE FUNCTION get_existing_or_create_lot(text, text, text[], text[])
RETURNS TABLE(_lot_id int, _start_timestamp double precision, _end_timestamp double precision, _name text, _application text, _param_type text, _param_value text) AS
$BODY$DECLARE
_param_name ALIAS FOR $1;
_param_application ALIAS FOR $2;
_param_types ALIAS FOR $3;
_param_values ALIAS FOR $4;
i int;
_param_type_id int;
_lot lot;

unit_table_index int;
unit_table_name text;
measurement_table_index int;
measurement_table_name text;
BEGIN
-- Parameters validation.
IF array_upper(_param_types, 1) != array_upper(_param_values, 1) THEN
RAISE EXCEPTION 'Inconsistency in array size';
END IF;

-- Find an existing lot.
SELECT INTO _lot * FROM lot WHERE name = _param_name AND application = _param_application;

-- Fall into creation code block.
IF FOUND THEN
PERFORM update_or_insert_params(_lot.lot_id, _param_types, _param_values);
RETURN QUERY EXECUTE 'SELECT * FROM get_lot_by_id($1)' USING _lot.lot_id;
RETURN;
END IF;

INSERT INTO lot(end_timestamp, name, application) VALUES(NULL, _param_name, _param_application) RETURNING * INTO _lot;
PERFORM update_or_insert_params(_lot.lot_id, _param_types, _param_values);

unit_table_index = _lot.lot_id;
unit_table_name = 'unit_' || unit_table_index;
measurement_table_index = _lot.lot_id;
measurement_table_name = 'measurement_' || measurement_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;

IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = measurement_table_name) THEN
EXECUTE 'CREATE TABLE ' || quote_ident(measurement_table_name) || '
(
measurement_id serial NOT NULL,
fk_unit_id int NOT NULL,
measurement_type text NOT NULL,
measurement_unit text NOT NULL,
lot_id int NOT NULL,
CHECK (lot_id = ' || (measurement_table_index) || '),

CONSTRAINT pk_measurement_' || measurement_table_index || '_id PRIMARY KEY (measurement_id),
CONSTRAINT fk_unit_' || measurement_table_index || '_id FOREIGN KEY (fk_unit_id)
REFERENCES unit_' || measurement_table_index || ' (unit_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
) INHERITS (measurement);';

EXECUTE 'CREATE INDEX idx_fk_unit_' || measurement_table_index || '_id
ON ' || quote_ident(measurement_table_name) || ' USING btree
(fk_unit_id);';

EXECUTE 'CREATE INDEX idx_measurement_value_' || measurement_table_index || '
ON ' || quote_ident(measurement_table_name) || ' USING btree
(value) WHERE value IS NULL;';

EXECUTE 'CREATE INDEX idx_lot_' || measurement_table_index || ' ON ' || quote_ident(measurement_table_name) || '(lot_id);';
END IF;

RETURN QUERY EXECUTE 'SELECT * FROM get_lot_by_id($1)' USING _lot.lot_id;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_existing_or_create_lot(text, text, text[], text[]) OWNER TO postgres;

Thanks and Regards
Yan Cheng CHEOK

--- On Mon, 4/26/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in partition.
> To: "Yan Cheng CHEOK" <yccheok(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Date: Monday, April 26, 2010, 2:04 PM
> Yan Cheng CHEOK <yccheok(at)yahoo(dot)com>
> writes:
> > 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.
>
> > Unfortunately, I get the run time error ;
> > 2010-04-26 13:28:28 MYTERROR:  deadlock
> detected   
>
> The reason for the error is probably that establishing the
> FK reference
> to table "lot" requires an exclusive lock on "lot", so each
> occurrence
> of this creation will serialize on that, in addition to
> anything else
> it might be locking.
>
> My opinion is that you're shooting yourself in the foot
> with a poorly
> chosen database layout.  Forget all the subtables and
> just have one
> big unit table.  It'll be far simpler and probably
> perform better too.
>
>            
> regards, tom lane
>