Foreign key wierdness
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.
"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.
regards, tom lane
-----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.
Import Notes
Resolved by subject fallback
"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
-----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.
Import Notes
Resolved by subject fallback
-----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 wierdnessI'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.
Import Notes
Resolved by subject fallback
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>
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
Import Notes
Reply to msg id not found: 3E2C6927.9060502@xs4all.be
-----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 wierdnessDidier 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 : amassive 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.
Import Notes
Resolved by subject fallback
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
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
-----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 wierdnessI 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.
Import Notes
Resolved by subject fallback
-----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 wierdnessWith 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.
Import Notes
Resolved by subject fallback
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
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
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?
--
Hannu Krosing <hannu@tm.ee>