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,
_applicationtext, _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
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;

    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@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in partition.
> To: "Yan Cheng CHEOK" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Monday, April 26, 2010, 2:04 PM
> Yan Cheng CHEOK <yccheok@yahoo.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
>