On 1 August 2011 18:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Aug 1, 2011 at 1:42 PM, Dean Rasheed <dean.a.rasheed@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