contrib/adddepend does not properly re-create multi-column foreign keys. - Mailing list pgsql-bugs

From Adam Buraczewski
Subject contrib/adddepend does not properly re-create multi-column foreign keys.
Date
Msg-id 20021201155027.GB690@localhost.localdomain
Whole thread Raw
Responses Re: contrib/adddepend does not properly re-create multi-column foreign keys.
List pgsql-bugs
Hallo,

I've used contrib/adddepend script (written in Perl) to move my
databases from PostgreSQL 7.2 to 7.3-style foreign key syntax, in
order to use new dependency information and get rid of those "create
constraint trigger" commands generated by pg_dump.  The script does a
very good job indeed.  It can be also downloaded from author's
website:

    http://www.rbt.ca/postgresql/upgrade.shtml

I noticed that both versions (attached to PostgreSQL 7.3 sources and
downloaded from author's website) have problems with multi-column
keys, which are not re-created properly.  For example, I had a table
of the following definition:

    create table foo (
        a integer not null,
        b integer not null,
        c integer,
        d integer,

        primary key (a, b),
        foreign key (c, d) references (a, b) match full
    );

After dumping this using pg_dump from PostgreSQL 7.2.3, and loading
the dump into PostgreSQL 7.3, adddepend script tried to create the
constraint with following command:

    alter table foo add foreign key (c, b) references (a, d) match full;

which didn't work of course, because columns b and d were swapped.

After some digging in the code, I found that when subroutine
"findForeignKeys" tries to parse trigger parameters, it loads first
two column names into $lcolumn_name and $fcolumn_name variables, and
all the rest into @junk array.  After this, it uses Perl pop()
function, each time twice, to get a pair of column names, and then
stores first pop'ped name as a foreign key column name and second as a
referenced column name.  This is wrong, because these names are
swapped this way.  Not only primary keys are swapped with foreign
keys, but the order of columns in a multi-column key is not preserved
either.  I think one should use shift() function instead, and it would
eliminate both problems.

Simply: the program takes column names from the wrong end of the array
:)

I already notified the author of the program and attached patches to
both original upgrade.pl and contrib/adddepend scripts.

Regards,

--
Adam Buraczewski <adamb@polbox.pl> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?

Attachment

pgsql-bugs by date:

Previous
From: Rod Taylor
Date:
Subject: Re: DROP COLUMN really work?
Next
From: Theodore Petrosky
Date:
Subject: Re: more information on OSX pg_dump problem