From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: surprising trigger/foreign key interaction |
Date: | 2009-08-13 17:17:44 |
Message-ID: | 4A844AB8.9030108@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 8/13/09 7:03 AM, Alvaro Herrera wrote:
> Tom Lane wrote:
>> Alvaro Herrera <alvherre(at)commandprompt(dot)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-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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-13 17:28:05 | Re: Hot standby and synchronous replication status |
Previous Message | Josh Berkus | 2009-08-13 17:10:51 | Re: Hot standby and synchronous replication status |