Thread: Foreign key wierdness

Foreign key wierdness

From
"Dave Page"
Date:
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.


Re: Foreign key wierdness

From
Tom Lane
Date:
"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
referencedtable.   (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.
        regards, tom lane


Re: Foreign key wierdness

From
"Dave Page"
Date:

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


Re: Foreign key wierdness

From
Tom Lane
Date:
"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


Re: Foreign key wierdness

From
Hannu Krosing
Date:
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>


Re: Foreign key wierdness

From
"Dave Page"
Date:

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

Didier?

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

Sorry, brain hiccup.

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

Well to be honest I'm having a hard time believing it, but having looked
at this in some depth, it's the only thing that the 2 versions of
pgAdmin are doing differently. Even the PostgreSQL logs agree with that.
I'm relying on Didier for test results though as I don't have a test
system I can use for this at the moment.

But it gives us something to try - Didier can you create a new database
please, and load the data from 2 tables. VACUUM ANALYZE, then add the
foreign key in psql using the syntax 1.4.2 uses. Then drop the database,
and load exactly the same data in the same way, VACUUM ANALYZE again,
and create the fkey using the qualified tablename syntax.

Thanks, Dave.


Re: Foreign key wierdness

From
"Dave Page"
Date:

> -----Original Message-----
> From: Hannu Krosing [mailto:hannu@tm.ee]
> Sent: 20 January 2003 18:05
> To: Dave Page
> Cc: Tom Lane; PostgreSQL Hackers Mailing List; Didier Moens
> Subject: Re: [HACKERS] Foreign key wierdness
>
>
> > 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 ?

No, in both case, just significantly more slowly with schema
qualification. Note that the qualification is added to the ALTER TABLE
statement though - PostgreSQL's qualifying the name in the SELECT for
every case.

Regards, Dave.


Re: Foreign key wierdness

From
Tom Lane
Date:
Didier Moens <moensd@xs4all.be> writes:
> I just formally tested on PostgreSQL 7.2.2 (logs sent to Dave), and the 
> results are perfectly inline with those from 7.2.3 : a massive slowdown 
> when upgrading from pgadminII 1.4.2 to 1.4.12.

I thought the complaint involved PG 7.3?  There is no schema syntax in 7.2.
        regards, tom lane


Re: Foreign key wierdness

From
"Dave Page"
Date:

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 20 January 2003 21:28
> To: Didier Moens
> Cc: Dave Page; PostgreSQL Hackers Mailing List
> Subject: Re: [HACKERS] Foreign key wierdness
>
>
> Didier Moens <moensd@xs4all.be> writes:
> > I just formally tested on PostgreSQL 7.2.2 (logs sent to Dave), and
> > the
> > results are perfectly inline with those from 7.2.3 : a
> massive slowdown
> > when upgrading from pgadminII 1.4.2 to 1.4.12.
>
> I thought the complaint involved PG 7.3?  There is no schema
> syntax in 7.2.

<puzzled> No there isn't. pgAdmin uses the same unqualified SQL in all
cases on 7.2.x...

Regards, Dave.


Re: Foreign key wierdness

From
Tom Lane
Date:
Didier Moens <Didier.Moens@dmb001.rug.ac.be> writes:
> I did some extensive testing using PostgreSQL 7.3.1 (logs and results 
> available upon request), and the massive slowdown is NOT related to 
> qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the 
> following change :

> pgAdminII 1.4.2 :
> -------------------
> CREATE TABLE articles (
>     article_id integer DEFAULT 
> nextval('"articles_article_id_key"'::text) NOT NULL,
> ...

> pgAdminII 1.4.12 :
> --------------------
> CREATE TABLE articles (
>     article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) 
> NOT NULL,
> ...

Ah-hah, and I'll bet that the column being linked to this one by the
foreign key constraint is still an integer?

> With two tables each containing some 20.000 entries, the fk creation 
> time between both of them increases from ~ 1.8 secs to ~ 221 secs.

Seems odd that the cost would get *that* much worse.  Maybe we need to
look at whether the FK checking queries need to include explicit casts
...
        regards, tom lane


Re: Foreign key wierdness

From
"Dave Page"
Date:

> -----Original Message-----
> From: Didier Moens [mailto:Didier.Moens@dmb001.rug.ac.be]
> Sent: 22 January 2003 16:05
> To: Dave Page
> Cc: Tom Lane; PostgreSQL Hackers Mailing List
> Subject: Re: [HACKERS] Foreign key wierdness
>
>
> I did some extensive testing using PostgreSQL 7.3.1 (logs and results
> available upon request), and the massive slowdown is NOT related to
> qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the
> following change :
>
> pgAdminII 1.4.2 :
> -------------------
> CREATE TABLE articles (
>     article_id integer DEFAULT
> nextval('"articles_article_id_key"'::text) NOT NULL,
> ...
>
> pgAdminII 1.4.12 :
> --------------------
> CREATE TABLE articles (
>     article_id bigint DEFAULT
> nextval('"articles_article_id_key"'::text)
> NOT NULL,
> ...

I'd never have guessed that one, despite noting that particular change
in the code some time ago. Oh well...

Anyway, that was changed in pgAdmin because I got a couple of complaints
about it's use of int4 for migrated serial/autonumber columns. One user
was migrating some *very* large numbers from a SQL Server.

From what Tom has said in his reponse, I think the answer for you Didier
is to remap your integer columns to int8 instead of int4 and see what
happens. When I get a couple of minutes I will look at putting a Serials
as... Option in the type map.

Regards, Dave.


Re: Foreign key wierdness

From
"Dave Page"
Date:

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 22 January 2003 20:30
> To: Didier Moens
> Cc: Dave Page; PostgreSQL Hackers Mailing List
> Subject: Re: [HACKERS] Foreign key wierdness
>
>
> > With two tables each containing some 20.000 entries, the fk creation
> > time between both of them increases from ~ 1.8 secs to ~ 221 secs.
>
> Seems odd that the cost would get *that* much worse.  Maybe
> we need to look at whether the FK checking queries need to
> include explicit casts ...

Does seem like it needs some work, though it could be argued that the
columns should be of the same type (which they were of course until I
got clever and increased the size of autonumber column that pgAdmin
creates!).

Anyway, as always, many thanks for your help and putting our testing
back on a sensible course after much head scratching!! :-)

Regards, Dave.


Re: Foreign key wierdness

From
Hannu Krosing
Date:
Tom Lane kirjutas K, 22.01.2003 kell 22:30:
> Didier Moens <Didier.Moens@dmb001.rug.ac.be> writes:
> > I did some extensive testing using PostgreSQL 7.3.1 (logs and results 
> > available upon request), and the massive slowdown is NOT related to 
> > qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the 
> > following change :
> 
> > pgAdminII 1.4.2 :
> > -------------------
> > CREATE TABLE articles (
> >     article_id integer DEFAULT 
> > nextval('"articles_article_id_key"'::text) NOT NULL,
> > ...
> 
> > pgAdminII 1.4.12 :
> > --------------------
> > CREATE TABLE articles (
> >     article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) 
> > NOT NULL,
> > ...
> 
> Ah-hah, and I'll bet that the column being linked to this one by the
> foreign key constraint is still an integer?

This should at least give out a NOTICE or ABORT or generate a functional
index, not a plain one.

> > With two tables each containing some 20.000 entries, the fk creation 
> > time between both of them increases from ~ 1.8 secs to ~ 221 secs.
> 
> Seems odd that the cost would get *that* much worse.  Maybe we need to
> look at whether the FK checking queries need to include explicit casts
> ...
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
-- 
Hannu Krosing <hannu@tm.ee>


Re: Foreign key wierdness

From
Didier Moens
Date:
Dear Tom, Dave,


Tom Lane wrote:

>Ah-hah, and I'll bet that the column being linked to this one by the
>foreign key constraint is still an integer?
>

It sure is ; being a PostgreSQL novice (BTW : many thanks to the whole 
of the PG development team for such an excellent product), I got on this 
track by means of 
http://archives.postgresql.org/pgsql-sql/2001-05/msg00395.php .


>With two tables each containing some 20.000 entries, the fk creation 
>time between both of them increases from ~ 1.8 secs to ~ 221 secs.
>  
>
>
>Seems odd that the cost would get *that* much worse.  Maybe we need to
>look at whether the FK checking queries need to include explicit casts
>...
>

Well, I reproduced the slowdown with some 20 to 30 different tables.
Anyway, glad I could be of some help, albeit only by testing some 
(probably quite meaningless) border cases ...  :)


Regards,
Didier

-- 

Didier Moens
-----
RUG/VIB - Dept. Molecular Biomedical Research - Core IT
tel ++32(9)2645309 fax ++32(9)2645348
http://www.dmb.rug.ac.be




Re: Foreign key wierdness

From
Didier Moens
Date:
Hi all,

Dave Page wrote:

>>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.
>>    
>>
>
>Well to be honest I'm having a hard time believing it, but having looked
>at this in some depth, it's the only thing that the 2 versions of
>pgAdmin are doing differently. Even the PostgreSQL logs agree with that.
>I'm relying on Didier for test results though as I don't have a test
>system I can use for this at the moment.
>
>But it gives us something to try - Didier can you create a new database
>please, and load the data from 2 tables. VACUUM ANALYZE, then add the
>foreign key in psql using the syntax 1.4.2 uses. Then drop the database,
>and load exactly the same data in the same way, VACUUM ANALYZE again,
>and create the fkey using the qualified tablename syntax.
>

I did some extensive testing using PostgreSQL 7.3.1 (logs and results 
available upon request), and the massive slowdown is NOT related to 
qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the 
following change :

pgAdminII 1.4.2 :
-------------------
CREATE TABLE articles (   article_id integer DEFAULT 
nextval('"articles_article_id_key"'::text) NOT NULL,
...

test=# \d articles                                       Table "public.articles"    Column      |         Type
|                         
 
Modifiers
-----------------+-----------------------+-------------------------------------------------------------article_id
|integer               | not null default 
 
nextval('"articles_article_id_key"'::text)...

pgAdminII 1.4.12 :
--------------------
CREATE TABLE articles (   article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) 
NOT NULL,
...

test=# \d articles                                       Table "public.articles"    Column      |         Type
|                         
 
Modifiers
-----------------+-----------------------+-------------------------------------------------------------article_id
|bigint                | not null default 
 
nextval('"articles_article_id_key"'::text)...


With two tables each containing some 20.000 entries, the fk creation 
time between both of them increases from ~ 1.8 secs to ~ 221 secs.


Regards,
Didier

-- 

Didier Moens
-----
RUG/VIB - Dept. Molecular Biomedical Research - Core IT
tel ++32(9)2645309 fax ++32(9)2645348
http://www.dmb.rug.ac.be




Re: Foreign key wierdness

From
Didier Moens
Date:
Dave Page wrote:

>>From what Tom has said in his reponse, I think the answer for you Didier
>is to remap your integer columns to int8 instead of int4 and see what
>happens. When I get a couple of minutes I will look at putting a Serials
>as... Option in the type map.
>

Thanks Dave, for all of your invested time.

I think the value of tools such as pgAdmin, which provide an almost 
bumpless cross-platform migration path, cannot be underestimated.


Regards,
Didier

-- 

Didier Moens
-----
RUG/VIB - Dept. Molecular Biomedical Research - Core IT
http://www.dmb.rug.ac.be