Re: Foreign key wierdness

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