Hi all,

A pgAdmin user has noticed that Foreign Keys take significantly longer
to create when migrating a database in pgAdmin in v1.4.12 than in
v1.4.2.

The only difference in the migration code between these releases, is
that pgAdmin now qualifies tables names with the schema name. The
following log snippets are from identical migrations from an Access
database to PostgreSQL 7.3.1:

pgAdmin 1.4.12
==============

Jan 19 16:34:26 lmb042 postgres[17986]: [78991-1] LOG:  query: ALTER
TABLE public.articles ADD CONSTRAINT productsarticles_fk FOREIGN
KEY(product_ref) REFERENCES public.products
Jan 19 16:34:26 lmb042 postgres[17986]: [78991-2]  (product_id) ON
DELETE CASCADE ON UPDATE CASCADE
Jan 19 16:34:26 lmb042 postgres[17986]: [78992] NOTICE:  ALTER TABLE
will create implicit trigger(s) for FOREIGN KEY check(s)
Jan 19 16:34:26 lmb042 postgres[17986]: [78993] LOG:  query: SELECT 1
FROM ONLY "public"."products" x WHERE "product_id" = $1 FOR UPDATE OF x
Jan 19 16:38:33 lmb042 postgres[17986]: [78994] LOG:  duration:
247.585771 sec

pgAdmin 1.4.2
=============

Jan 19 15:48:56 lmb042 postgres[17542]: [78991-1] LOG:  query: ALTER
TABLE articles ADD CONSTRAINT productsarticles_fk FOREIGN
KEY(product_ref) REFERENCES products (product_id)
Jan 19 15:48:56 lmb042 postgres[17542]: [78991-2]  ON DELETE CASCADE ON
UPDATE CASCADE
Jan 19 15:48:56 lmb042 postgres[17542]: [78992] NOTICE:  ALTER TABLE
will create implicit trigger(s) for FOREIGN KEY check(s)
Jan 19 15:48:56 lmb042 postgres[17542]: [78993] LOG:  query: SELECT 1
FROM ONLY "public"."products" x WHERE "product_id" = $1 FOR UPDATE OF x
Jan 19 15:48:58 lmb042 postgres[17542]: [78994] LOG:  duration: 1.988144
sec

A similar select query follows every key creation, but is not issued by
pgAdmin, or the user's application(s), so I assume PostgreSQL is doing
it for some reason. Any ideas why it takes so long (for the same data)
when pgAdmin qualifies the table name in the ALTER statement?

Thanks, Dave.

Mon, 20 Jan 2003 10:18:07 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Foreign key wierdness
"Dave Page" <dpage@vale-housing.co.uk> writes:
> A pgAdmin user has noticed that Foreign Keys take significantly longer
> to create when migrating a database in pgAdmin in v1.4.12 than in
> v1.4.2.

The only reason ADD FOREIGN KEY would take a long time is if
(a) it has to wait awhile to get exclusive lock on either
    the referencing or referenced table; and/or
(b) it takes a long time to verify that the existing entries
    in the referencing table all have matches in the referenced table.
    (that's the behind-the-scenes query you see)

I'm betting that the table was busy, or there was a lot more data
present in the one case, or you hadn't ever vacuumed/analyzed one or
both tables and so a bad plan was chosen for the verification query.
The schema reference is definitely not the issue.

			regards, tom lane

Mon, 20 Jan 2003 10:27:38 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign key wierdness 