(Debian Bug#41223) problem with cascaded updates with refint

Package: postgresql-contrib
Version: 6.5-2

I wonder whether anybody on the postgresql lists can reproduce the
following problem:

Cascaded updates tend to write old data on top of new, as the following
minimalistic example shows:

CREATE TABLE "tipos" (
"tipo" text NOT NULL,
"designacao" text DEFAULT '');
CREATE TABLE "duracoes" (
"tipo" text DEFAULT '' NOT NULL,
"duracao" timespan NOT NULL);

CREATE FUNCTION "check_primary_key" ( ) RETURNS opaque AS '/usr/lib/postgresql/modules/refint.so' LANGUAGE 'C';
CREATE FUNCTION "check_foreign_key" ( ) RETURNS opaque AS '/usr/lib/postgresql/modules/refint.so' LANGUAGE 'C';

COPY "tipos" FROM stdin;
P Prática
T Teórica
S Seminário
TP Teorico-prática
\.
COPY "duracoes" FROM stdin;
P @ 3 hours
T @ 1 hour
T @ 1 hour 30 mins
TP @ 1 hour 30 mins
TP @ 2 hours
TP @ 3 hours
\.
CREATE UNIQUE INDEX "tipos_pkey" on "tipos" using btree ( "tipo" "text_ops" );
CREATE UNIQUE INDEX "duracoes_pkey" on "duracoes" using btree ( "tipo" "text_ops", "duracao" "timespan_ops" );
CREATE TRIGGER "tipos_trigger_d" BEFORE DELETE ON "tipos" FOR EACH ROW EXECUTE PROCEDURE check_foreign_key ('1', 'cascade', 'tipo', '"duracoes"', 'tipo');
CREATE TRIGGER "tipos_trigger_u" AFTER UPDATE ON "tipos" FOR EACH ROW EXECUTE PROCEDURE check_foreign_key ('1', 'cascade', 'tipo', '"duracoes"', 'tipo');
CREATE TRIGGER "tipos_duracoes" BEFORE INSERT OR UPDATE ON "duracoes" FOR EACH ROW EXECUTE PROCEDURE check_primary_key ('tipo', '"tipos"', 'tipo');

After setting up a database as described above, do the following:

=> update tipos set tipo='Tx' where tipo='T';
UPDATE 1
=> select * from tipos;
tipo|designacao
----+---------------
P |Prática
S |Seminário
TP |Teorico-prática
Tx |Teórica
(4 rows)

=> select * from duracoes;
tipo|duracao
----+----------------
P |@ 3 hours
TP |@ 1 hour 30 mins
TP |@ 2 hours
TP |@ 3 hours
Tx |@ 1 hour
Tx |@ 1 hour 30 mins
(6 rows)

So far so good! Now:

=> update tipos set tipo='Px' where tipo='P';
UPDATE 1
=> select * from tipos;
tipo|designacao
----+---------------
S |Seminário
TP |Teorico-prática
Tx |Teórica
Px |Prática
(4 rows)

=> select * from duracoes;
tipo|duracao
----+----------------
TP |@ 1 hour 30 mins
TP |@ 2 hours
TP |@ 3 hours
Tx |@ 1 hour
Tx |@ 1 hour 30 mins
Tx |@ 3 hours
^^ should be Px, NOT Tx
(6 rows)

This makes cascaded updates unusable, unfortunately... I can reproduce the
same behaviour on a PC, as well. I am running slink, so I compiled the
packages myself, from the debianized sources.

Thanks for any help!

Carlos Fonseca

-- System Information
Debian Release: 2.1
Kernel Version: Linux diana 2.2.7 #1 Sat May 8 19:57:23 WEST 1999 sparc unknown

Versions of the packages postgresql-contrib depends on:
ii postgresql 6.5-2 Object-relational SQL database, descended fr