Re: surprising trigger/foreign key interaction
От | Josh Berkus |
---|---|
Тема | Re: surprising trigger/foreign key interaction |
Дата | |
Msg-id | 4A844AB8.9030108@agliodbs.com обсуждение исходный текст |
Ответ на | Re: surprising trigger/foreign key interaction (Alvaro Herrera <alvherre@commandprompt.com>) |
Список | pgsql-hackers |
On 8/13/09 7:03 AM, Alvaro Herrera wrote: > Tom Lane wrote: >> Alvaro Herrera <alvherre@commandprompt.com> writes: > >>> I imagine this is so because of some old fiddling to get semantics just >>> right for obscure corner cases, but it feels wrong nevertheless. >> I suspect it was reluctance to use the EvalPlanQual semantics (which >> are pretty bogus in their own way) for perfectly deterministic >> single-transaction cases. > > I suspect the FK trigger messing up the visibility is an obscure corner > case too :-( Yes, but it's one which happens frequently. I've already had to debug a client case where a client had a before trigger, and after trigger, and a self-join FK. That seems like a bizarre arrangement, but for a proximity tree (which we're going to see a lot more of thanks to WITH RECURSIVE) it actually makes a lot of sense. The result is that you can get a *successful* transaction, with no error, that nevertheless results in rows which are inconsistent with the FK -- silent data corruption. I had to tell the user to disable the FK and maintain consistency by trigger as well, which doesn't reflect well on our devotion to avoiding data corruption. This is 100% reproduceable; test case below my sig. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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-441442 | 441 | 45 | tree441-442443| 442 | 45 | tree442-443444 | 443 | 45 | tree443-444445 | 444 | 45 | tree444-445446 | 445 | 45 | tree445-446447 | 446 | 45 | tree446-447448 | 447 | 45 | tree447-448449 | 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-441443 | 441 | 45 | tree442-443445 | 443 | 45 | tree444-445447 | 445 | 45 | tree446-447449 | 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-hackers по дате отправления: