Re: Foreign key wierdness

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 20 January 2003 15:28
> To: Dave Page
> Cc: PostgreSQL Hackers Mailing List; Didier Moens
> Subject: Re: [HACKERS] 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.

Thing is Tom, this issue can be reproduced *every* time, without fail.
The difference is huge as well, it's a difference of a couple of
seconds, the total migration will take around 1704.67 seconds without
schema qualification, and 11125.99 with schema qualification to quote
one test run.

As I understand it, this has be tried on a test box, and a production
box (running RedHat builds of 7.3.1), and is a migration of the same
source Access database.

I've been looking at his for some time now (couple of weeks or more),
and the only thing I can find is the SELECT ... FOR UPDATE in the
PostgreSQL logs that I quoted. These exactly follow *every* fkey
creation, and are definately not issued by pgAdmin. If they were issued
by another app or user, how come they exactly follow each fkey creation,
and are on the reference table of the fkey?

Regards, Dave.