Re: CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks

On Apr 4, 2006, at 12:53 AM, Tom Lane wrote:

> "Thomas F. O'Connell" <tfo@sitening.com> writes:
>> I'm dealing with an application that can potentially do ad hoc DDL.
>> It uses a PG/pgSQL function, and the only DDL statements in the
>> function are CREATE TABLE and CREATE INDEX statements. But I'm
>> noticing that during the backup process (with pg_dump or pg_dumpall),
>> the function is acquiring ACCESS EXCLUSIVE locks and bringing the
>> application to its knees.
>
> Please provide a test case.  AFAIR neither of those should take any
> AccessExclusive locks --- except on the new table, which shouldn't
> matter because pg_dump won't see it.

Below is a sketch of the function where the only difference with
reality is identifier names. I'm pretty sure I obfuscated it
consistently.

As for how this plays out in the real world, a pg_dumpall will start
and run for a few hours. Sometime during that, this function might
get called. When it does, an ACCESS EXCLUSIVE lock is held against
the table identified as t13, here directly referenced only as a
FOREIGN KEY.

This function is only DDL statements and calls no other functions.

CREATE OR REPLACE FUNCTION takes_access_exclusive_lock(character
varying) RETURNS character varying
     AS '
     DECLARE
         -- alias
         id ALIAS FOR $1;

         -- sql variables
         create_child1 VARCHAR;
         create_child2 VARCHAR;
         create_child3 VARCHAR;
         create_child4 VARCHAR;
         create_child5 VARCHAR;
         create_child6 VARCHAR;
         create_child7 VARCHAR;
         create_child8 VARCHAR;
         create_child9 VARCHAR;
         create_child10 VARCHAR;
         create_child11 VARCHAR;
         create_child12 VARCHAR;
         create_indexes VARCHAR;

         -- helpers
         table_prefix VARCHAR;
     BEGIN
         table_prefix := ''child_'' || id;

         create_child1 :=  ''
CREATE TABLE '' || table_prefix || ''_t1 (
   CONSTRAINT '' || table_prefix || ''_t1_pkey PRIMARY KEY (id)
) INHERITS (t1) WITHOUT OIDS '';

         create_child2 :=  ''
CREATE TABLE '' || table_prefix || ''_t2 (
   CONSTRAINT '' || table_prefix || ''_t2_pkey PRIMARY KEY (id)
) INHERITS (t2) WITHOUT OIDS '';

         create_child3 := ''
CREATE TABLE '' || table_prefix || ''_t3 (
   CONSTRAINT '' || table_prefix || ''_t3_pkey PRIMARY KEY (id1, id2),
   CONSTRAINT '' || table_prefix || ''_t3_fkey2 FOREIGN KEY (id2)
     REFERENCES public.t13 (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
   CONSTRAINT '' || table_prefix || ''_t3_fkey1 FOREIGN KEY (id1)
     REFERENCES public.'' || table_prefix || ''_t1 (id) ON UPDATE
RESTRICT ON DELETE RESTRICT
) INHERITS (t3)  WITHOUT OIDS '';

         create_child4 := ''
CREATE TABLE '' || table_prefix || ''_t4 (
   CONSTRAINT '' || table_prefix || ''_t4_pkey PRIMARY KEY (id)
) INHERITS (t4)  WITHOUT OIDS '';

         create_child5 := ''
CREATE TABLE '' || table_prefix || ''_t5 (
   CONSTRAINT '' || table_prefix || ''_t5_pkey PRIMARY KEY (id, ts),
   CONSTRAINT '' || table_prefix || ''_t5_fkey FOREIGN KEY (id)
     REFERENCES public.'' || table_prefix || ''_t4 (id) ON UPDATE
RESTRICT ON DELETE RESTRICT
) INHERITS (t5)  WITHOUT OIDS '';

         create_child6 := ''
CREATE TABLE '' || table_prefix || ''_t6 (
) INHERITS (t6)  WITHOUT OIDS '';

         create_child7 := ''
CREATE TABLE '' || table_prefix || ''_t7 (
   CONSTRAINT '' || table_prefix || ''_t7_pkey PRIMARY KEY (id),
   CONSTRAINT '' || table_prefix || ''_t7_fkey FOREIGN KEY (id)
     REFERENCES public.'' || table_prefix || ''_t4 (id) ON UPDATE
RESTRICT ON DELETE RESTRICT
) INHERITS (t7)  WITHOUT OIDS '';

         create_child8 := ''
CREATE TABLE '' || table_prefix || ''_t8 (
   CONSTRAINT '' || table_prefix || ''_t8_pkey PRIMARY KEY (id),
   CONSTRAINT '' || table_prefix || ''_t8_fkey FOREIGN KEY (id)
     REFERENCES public.'' || table_prefix || ''_t4 (id) ON UPDATE
RESTRICT ON DELETE RESTRICT
) INHERITS (t8)  WITHOUT OIDS '';

         create_child9 := ''
CREATE TABLE '' || table_prefix || ''_t9 (
   CONSTRAINT '' || table_prefix || ''_t9_pkey PRIMARY KEY (id),
   CONSTRAINT '' || table_prefix || ''_id2_id3_unique_idx UNIQUE
(id2, id3)
) INHERITS (t9)  WITHOUT OIDS '';

         create_child10 := ''
CREATE TABLE '' || table_prefix || ''_t10 (
   CONSTRAINT '' || table_prefix || ''_t10_pkey PRIMARY KEY (id)
) INHERITS (t10) WITHOUT OIDS '';

         create_child11 := ''
CREATE TABLE '' || table_prefix || ''_t11 (
   CONSTRAINT '' || table_prefix || ''_t11_pkey PRIMARY KEY (id1,
id2, col1, col2),
   CONSTRAINT '' || table_prefix || ''_t11_fkey1 FOREIGN KEY (id1)
     REFERENCES t14 (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
   CONSTRAINT '' || table_prefix || ''_t11_fkey2 FOREIGN KEY (id2)
     REFERENCES '' || table_prefix || ''_t10 (id) ON UPDATE RESTRICT
ON DELETE RESTRICT
) INHERITS (t11) WITHOUT OIDS '';

         create_child12 := ''
CREATE TABLE '' || table_prefix || ''_t12 (
   CONSTRAINT '' || table_prefix || ''_t12_pkey PRIMARY KEY (id)
) INHERITS (t12) WITHOUT OIDS '';

         create_indexes := ''
CREATE INDEX t1_'' || id || ''_col1_idx ON '' || table_prefix ||
''_t1 (col1);
CREATE INDEX t1_'' || id || ''_col2_idx ON '' || table_prefix || ''_t1
( col2 );
CREATE INDEX t1_'' || id || ''_lower_col1_idx ON '' || table_prefix
|| ''_t1(lower(col1));
CREATE INDEX t1_'' || id || ''_col2_col3_col4_idx ON '' ||
table_prefix || ''_t1( col2, lower( col3 ), lower( col4 ) );

CREATE INDEX t3_'' || id || ''_id2_idx ON '' || table_prefix || ''_t3
( id2 );

CREATE INDEX t4_'' || id || ''_id2_idx ON '' || table_prefix || ''_t4
( id2 );
CREATE INDEX t4_'' || id || ''_id3_idx ON '' || table_prefix || ''_t4
( id3 );
CREATE INDEX t4_'' || id || ''_col1_idx ON '' || table_prefix || ''_t4
( col1 );
CREATE INDEX t4_'' || id || ''_col2_idx ON '' || table_prefix || ''_t4
( col2 );

CREATE INDEX t6_'' || id || ''_id_idx ON '' || table_prefix || ''_t6
( id );

CREATE INDEX t7_'' || id || ''_col1_idx ON '' || table_prefix || ''_t7
( col1 );

CREATE INDEX t5_'' || id || ''_col1_idx ON '' || table_prefix || ''_t5
( col1 );

CREATE INDEX t9_'' || id || ''_id3_idx ON '' || table_prefix || ''_t9
( id3 );
CREATE INDEX t9_'' || id || ''_id4_idx ON '' || table_prefix || ''_t9
( id4 );
         '';

         EXECUTE create_child1;
         EXECUTE create_child2;
         EXECUTE create_child3;
         EXECUTE create_child4;
         EXECUTE create_child5;
         EXECUTE create_child6;
         EXECUTE create_child7;
         EXECUTE create_child8;
         EXECUTE create_child9;
         EXECUTE create_child10;
         EXECUTE create_child11;
         EXECUTE create_child12;
         EXECUTE create_indexes;

         -- Since it will die on an error, return TRUE
         RETURN ''TRUE'';
     END;
' LANGUAGE plpgsql;

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)