Skip site navigation (1) Skip section navigation (2)

Table rewrites vs. pending AFTER triggers

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 (view raw or flat)
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

Responses

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2008-01-01 21:20:17
Subject: Re: Index Page Split logging
Previous:From: Simon RiggsDate: 2008-01-01 20:55:58
Subject: Re: Index Page Split logging

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group