Re: visibility rules for AFTER UPDATE Constraint Triggers Function

Richard Broersma Jr <rabroersma(at)yahoo(dot)com> writes:
> --- On Tue, 1/1/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Please provide a self-contained example.

> Basically the example demonstrates the difference a single tuple UPDATE when preformed the following two ways:

> UPDATE table ...; -- Sees OLD.

> BEGIN; UPDATE table ...; COMMIT; --Sees NEW.

> I was my understanding that all single DML statement are wrapped in their own transaction so I thought that these two statements should preform the same.

[ pokes at it... ] The reason is that you defined both the trigger and
the testing function as STABLE, which means that they see a snapshot of
the database as of the start of the calling SQL command. In the first
case that's the UPDATE, in the second it's the COMMIT.

If you remove the STABLE label from the trigger function then both
variants act the same, because the trigger can see the results of
the command that called it:

d2=# UPDATE Looptimelines SET enddate = enddate + INTERVAL '5 DAYS' WHERE endproject_code = '02U20420';
NOTICE: After performing the UPDATE operation, the NEW record is
visible before the commit.
UPDATE 1
d2=# BEGIN; UPDATE Looptimelines SET enddate = enddate + INTERVAL '5 DAYS' WHERE endproject_code = '02U20420'; COMMIT;
BEGIN
UPDATE 1
NOTICE: After performing the UPDATE operation, the NEW record is
visible before the commit.
COMMIT
d2=#

By and large I'd not recommend marking trigger functions as STABLE
(or IMMUTABLE). You usually want 'em to see current data.

Because the sample_for_new_or_old() function is STABLE, it sees what
its calling statement sees (in this case, the PERFORM in the trigger).
That probably is OK --- it seems likely that you want both probes in
that function to use the same snapshot, which they will if it's
STABLE.

regards, tom lane