Re: Foreign key wierdness

On Mon, 2003-01-20 at 15:47, Dave Page wrote:
> > -----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.

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@tm.ee>