Thread: surprising trigger/foreign key interaction
While working on some code I ran into a problem where some DELETE requests would get seamingly ignored after a while I managed to boil it down to: CREATE TABLE foo (a INT PRIMARY KEY, b int); CREATE TABLE bar (x int PRIMARY KEY, a int references foo(a) ON DELETE SET NULL); INSERT INTO foo VALUES (1,10); INSERT INTO bar VALUES (99,1); CREATE OR REPLACE FUNCTION bar_proc() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'INSERT') THEN RETURN NEW; ELSIF (TG_OP = 'UPDATE') THEN RETURN NEW; ELSIF (TG_OP = 'DELETE') THEN DELETE FROM foo WHERE a=1; RETURN OLD; END IF; RETURN OLD; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER bar_tr BEFORE INSERT OR DELETE ON bar FOR EACH ROW EXECUTE PROCEDURE bar_proc(); DELETE FROM bar where x=99; which results in: CREATE TABLE INSERT 0 1 INSERT 0 1 CREATE FUNCTION CREATE TRIGGER DELETE 0 the "surprise" here was that the delete is getting silently surpressed even though the original Qual still holds and afaik should result in the row deleted. Is that somehow expected behaviour or a bug(or at least something that should get documented somehow)? Stefan
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> the "surprise" here was that the delete is getting silently surpressed
> even though the original Qual still holds and afaik should result in the
> row deleted.
The delete from foo acts first (since you put it in a BEFORE trigger).
After the trigger comes back, the outer delete finds itself trying
to delete a row that was already updated by a later-starting command in
the same transaction. So it doesn't do anything to that row (cf the
HeapTupleSelfUpdated cases in execMain.c). I think this is documented
someplace but don't remember where.
regards, tom lane
Tom Lane wrote: > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes: > > the "surprise" here was that the delete is getting silently surpressed > > even though the original Qual still holds and afaik should result in the > > row deleted. > > The delete from foo acts first (since you put it in a BEFORE trigger). > After the trigger comes back, the outer delete finds itself trying > to delete a row that was already updated by a later-starting command in > the same transaction. Hmm, I misread the code when talking to Stefan on IM. I followed the path that heap_update returns HeapTupleUpdated, which proceeds to do EvalPlanQual and attempt to update the updated version of the row. However I'm guessing that what actually happens is that heap_update is returning HeapTupleSelfUpdated instead, which the code states as /* nothing to do */. I imagine this is so because of some old fiddling to get semantics just right for obscure corner cases, but it feels wrong nevertheless. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> However I'm guessing that what actually happens is that heap_update is
> returning HeapTupleSelfUpdated instead, which the code states as
> /* nothing to do */.
Yeah.
> 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.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> However I'm guessing that what actually happens is that heap_update is >> returning HeapTupleSelfUpdated instead, which the code states as >> /* nothing to do */. > > Yeah. > >> 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. still the current behaviour feels quite wrong because even after the update the modified tuple still satisfies the WHERE clause of the DELETE but still it won't actually get deleted. Stefan
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 :-( -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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)