Combination of Triggers and self-FKs produces inconsistent data
От | Josh Berkus |
---|---|
Тема | Combination of Triggers and self-FKs produces inconsistent data |
Дата | |
Msg-id | 4980C629.10602@agliodbs.com обсуждение исходный текст |
Ответы |
Re: Combination of Triggers and self-FKs produces inconsistent data
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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)
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Teodor SigaevДата:
Сообщение: Re: server crash when tsearch2 function is called from update trigger
Следующее
От: Tom LaneДата:
Сообщение: Re: Combination of Triggers and self-FKs produces inconsistent data