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

Re: Compressing the AFTER TRIGGER queue

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Compressing the AFTER TRIGGER queue
Date: 2011-08-01 18:27:47
Message-ID: CAEZATCXBdfryeZmXsM8UXVMgCR5XGi5Y+vXzWZ15nhRgGORzYQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-08-01 18:56:57
Subject: Re: Compressing the AFTER TRIGGER queue
Previous:From: Andrew DunstanDate: 2011-08-01 18:24:58
Subject: Re: libedit memory stomp is apparently fixed in OS X Lion

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