On 1 August 2011 18:55, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Mon, Aug 1, 2011 at 1:42 PM, Dean Rasheed <dean.a.rash...@gmail.com> >> wrote: >>>> Don't we already do that when pruning HOT chains? > >>> I thought that only happens after the transaction is committed, and >>> old enough, whereas the trigger code only needs to follow the chain in >>> the updating transaction. > >> Hmm, true. > >> I worry a bit that this might foreclose possible future optimization >> of the "self update" case, which is a known pain point. Am I wrong to >> worry? > > I think it might be OK if you explicitly verify that xmin/cmin of the > linked-to tuple matches the (sub)transaction/command that queued the > trigger event. I don't recall whether the trigger code does that > already; I think there is some related test but it might not be that > strict. > > There's also a definitional issue involved: if a transaction updates the > same tuple twice, in the presence of a deferred update trigger for the > table, is it supposed to (eventually) fire the trigger for both update > actions or only the last one? I have a feeling we might already be > locked into the second choice, but if not, this would probably force it. >
Do you mean this sort of case: DROP TABLE IF EXISTS foo; CREATE TABLE foo(a int); INSERT INTO foo VALUES(1); CREATE OR REPLACE FUNCTION foo_trig_fn() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'In foo_trig_fn(): old.a=%, new.a=%', old.a, new.a; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE CONSTRAINT TRIGGER foo_trig AFTER UPDATE ON foo DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE foo_trig_fn(); BEGIN; UPDATE foo SET a=a+1; UPDATE foo SET a=a+1; COMMIT; In this case we currently fire the trigger twice (once for each update) when the transaction commits, and the new code behaves the same. So at commit time you get: NOTICE: In foo_trig_fn(): old.a=1, new.a=2 NOTICE: In foo_trig_fn(): old.a=2, new.a=3 Thinking back to the deferred PK checking trigger, I thought that in this sort of case it is the trigger's responsibility to check that the tuple(s) it is given are not dead. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
