

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

Mon, 20 Jan 2003 15:47:51 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: Foreign key wierdness 
"Dave Page" <dpage@vale-housing.co.uk> writes:
> Thing is Tom, this issue can be reproduced *every* time, without fail.

And have you vacuumed or analyzed yet?  Or possibly you are short an
index or two (you really need indexes on both the referencing and
referenced columns).

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

No, I told you: those are the internal verification query (it comes from
RI_FKey_check_ins(), if you want to look).

If you really think the schema qualification has something to do with
it, try issuing the ADD FOREIGN KEY command manually in psql, with and
without schema name.

			regards, tom lane

Mon, 20 Jan 2003 11:07:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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>

20 Jan 2003 18:05:17 +0000
Hannu Krosing <hannu@tm.ee>
Re: Foreign key wierdness