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