Re: Foreign key wierdness

On Mon, 2003-01-20 at 15:47, Dave Page wrote:
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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(at)vale-housing(dot)co(dot)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.

can you try running ANALYZE (or VACUUM ANALYZE) after importing data but
before creating the foreign keys ?

> 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.

does this SELECT ... FOR UPDATE occur only when schemas are used ?

> 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?

I think Tom was trying to tell that the backend code indeed runs this,
but that it should not be that slow.

--
Hannu Krosing <hannu(at)tm(dot)ee>