Re: Compressing the AFTER TRIGGER queue

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