Re: surprising trigger/foreign key interaction - Mailing list 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)