From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Table rewrites vs. pending AFTER triggers |
Date: | 2008-01-01 21:09:49 |
Message-ID: | 20731.1199221789@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Some thought about bug #3847 led me to the following test case:
create table t1(f1 int);
create or replace function t1trig() returns trigger as $$
begin
raise notice 'f1 = %', new.f1;
return new;
end$$ language plpgsql;
create constraint trigger t1t after insert on t1
initially deferred for each row
execute procedure t1trig();
insert into t1 values('42');
insert into t1 values('43');
delete from t1;
begin;
insert into t1 values('44');
alter table t1 alter column f1 type text;
commit;
which fails at the COMMIT with
ERROR: failed to fetch new tuple for AFTER trigger
the reason being of course that the ALTER has rewritten the table and
put the f1=44 tuple at a different TID than what is recorded in the
pending-trigger-events list.
I don't think that this is exactly the problem that the bug reporter
is complaining of, since he wasn't using a DEFERRED trigger, but it
seems like a real hazard anyway.
We have already noted a related problem with respect to TRUNCATE,
and fixed it by forbidding TRUNCATE when there are any pending
trigger events on the target relation. (We only need to consider
local pending events, since locking will prevent this type of
problem between two different backends.)
I think that we need to put in a similar restriction for CLUSTER and
at least the table-rewriting forms of ALTER TABLE. Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.
Comments?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2008-01-01 21:20:17 | Re: Index Page Split logging |
Previous Message | Simon Riggs | 2008-01-01 20:55:58 | Re: Index Page Split logging |