(Debian Bug#41223) cascaded updates with refint insert bogus data

Package: postgresql-contrib
Version: 6.5-2

Dear PostgreSQL hackers,

I have sent this message to pgsql-general and so far received no reply.
This bug seems to be 100% reproducible on Linux (i386 and sparc). If this
problem is specific to Debian, then it would help me to know that, too.

Cascaded updates tend to write old data on top of new, as the following
minimal 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