Combination of Triggers and self-FKs produces inconsistent data

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Combination of Triggers and self-FKs produces inconsistent data
Date: 2009-01-28 20:55:05
Message-ID: 4980C629.10602@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Version: 8.3.5
Install: self-compile on 64-bit Ubuntu Linux
also reproduced by AndrewSN on another platform
Summary: self-referential FKs are not enforced properly in the
presence of BEFORE triggers
Test Case:

-- create two tables, one of which is the master table (reftable) the
other of which is a child which contains a tree structure (treetab):

create table reftable(
refid int primary key,
refname text
);

create table treetab (
id int primary key,
parent int,
refid int not null references reftable(refid) on delete cascade,
name text
);

-- now create a trigger function to maintain the integrity of the trees
in treetab by "pulling up"
-- each node to its parent if intermediate nodes get deleted
-- this trigger is inherently flawed and won't work with the FK below

create function treemaint () returns trigger as $t$
begin
update treetab set parent = OLD.parent
where parent = OLD.id;
return OLD;
end; $t$ language plpgsql;

create trigger treemaint_trg before delete on treetab
for each row execute procedure treemaint();

-- populate reftable

insert into reftable
select i, ( 'Ref' || i::TEXT ) from generate_series(1,100) as g(i);

-- populate treetab with 10 rows each pointing to reftable

insert into treetab (id, refid)
select i, (( i / 10::INT ) + 1 )
from generate_series (1,900) as g(i);

-- create trees in treetab. for this simple example each treeset is
just a chain with each child node
-- pointing to one higher node

update treetab set parent = ( id - 1 )
where id > (
select min(id) from treetab tt2
where tt2.refid = treetab.refid);

update treetab set "name" = ('tree' || parent::TEXT || '-' || id::TEXT);

-- now create a self-referential FK to enforce tree integrity. This
logically breaks the trigger

alter table treetab add constraint selfref foreign key (parent)
references treetab (id);

-- show tree for id 45

select * from treetab where refid = 45;

id | parent | refid | name
-----+--------+-------+-------------
440 | | 45 |
441 | 440 | 45 | tree440-441
442 | 441 | 45 | tree441-442
443 | 442 | 45 | tree442-443
444 | 443 | 45 | tree443-444
445 | 444 | 45 | tree444-445
446 | 445 | 45 | tree445-446
447 | 446 | 45 | tree446-447
448 | 447 | 45 | tree447-448
449 | 448 | 45 | tree448-449

-- now, we're going to delete the tree. This delete should fail with an
error because the
-- trigger will violate "selfref"

delete from reftable where refid = 45;

-- however, it doesn't fail. it reports success, and some but not all
rows from treetab
-- are deleted, leaving the database in an inconsistent state.

select * from treetab where refid = 45;

id | parent | refid | name
-----+--------+-------+-------------
441 | | 45 | tree440-441
443 | 441 | 45 | tree442-443
445 | 443 | 45 | tree444-445
447 | 445 | 45 | tree446-447
449 | 447 | 45 | tree448-449

-- this means we now have rows in the table which
-- violate the FK to reftable.

postgres=# select * from reftable where refid = 45;
refid | refname
-------+---------
(0 rows)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2009-01-29 00:00:22 Re: Combination of Triggers and self-FKs produces inconsistent data
Previous Message Teodor Sigaev 2009-01-28 18:47:35 Re: server crash when tsearch2 function is called from update trigger