Thread: (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

Carlos Fonseca wrote:
> 
> 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:

Unfortunately, when I wrote refint.c ~ 2.5 years ago I used
DELETE for both cascade UPDATE and DELETE. I don't remember why.
Massimo Lambertini (massimo.lambertini@everex.it) changed
refint.c to performe UPDATE of foreign keys on UPDATE of primary
ones, but he did error: he uses 1st update new primary key value 
in UPDATE _foreign_table_ SET and so execution plan is prepared,
saved, used with this value. Paramater ($1...$n) should be used there.
I have no time to fix it, sorry. Ask him or learn PL/pgSQL and write
trigger youself.

Vadim