Foreign key wierdness

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "PostgreSQL Hackers Mailing List" <pgsql-hackers(at)postgresql(dot)org>
Cc: "Didier Moens" <moensd(at)xs4all(dot)be>
Subject: Foreign key wierdness
Date: 2003-01-20 10:18:07
Message-ID: 03AF4E498C591348A42FC93DEA9661B8857C@mail.vale-housing.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Adrian 'Dagurashibanipal' von Bidder 2003-01-20 10:47:53 Re: Options for growth
Previous Message Daniel Kalchev 2003-01-20 09:31:56 Re: Options for growth