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?
