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 <[EMAIL PROTECTED]> * 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?
--- upgrade.pl.orig 2002-09-05 17:30:48.000000000 +0200
+++ upgrade.pl 2002-11-30 17:53:27.000000000 +0100
@@ -207,7 +207,7 @@
my $ref_cols = "$fcolumn_name";
# Perhaps there is more than a single column
- while ($lcolumn_name = pop(@junk) and $fcolumn_name =
pop(@junk)) {
+ while ($lcolumn_name = shift(@junk) and $fcolumn_name =
+shift(@junk)) {
$key_cols .= ", $lcolumn_name";
$ref_cols .= ", $fcolumn_name";
}
--- adddepend.orig 2002-10-18 20:41:19.000000000 +0200
+++ adddepend 2002-12-01 14:58:29.000000000 +0100
@@ -221,7 +221,7 @@
my $ref_cols = "$fcolumn_name";
# Perhaps there is more than a single column
- while ($lcolumn_name = pop(@junk) and $fcolumn_name =
pop(@junk)) {
+ while ($lcolumn_name = shift(@junk) and $fcolumn_name =
+shift(@junk)) {
$key_cols .= ", $lcolumn_name";
$ref_cols .= ", $fcolumn_name";
}
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
