Thread: visibility rules for AFTER UPDATE Constraint Triggers Function

I have a PLpgSQL AFTER UPDATE constraint trigger that calls a trigger function with an embedded select statement.

The select statement queries the affected table and returns a mult-result set that includes the record equivalent to
NEW.

However, It seems the select doesn't yet see the non-committed records. I.E. the record that should be equal to NEW is
infact equal to OLD. 

Is this correct? Is there a way to adjust the visibility so that yet-to-be committed tuples are seen by the trigger
function?

Regards,
Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:
> However, It seems the select doesn't yet see the non-committed records. I.E. the record that should be equal to NEW
isin fact equal to OLD. 

Please provide a self-contained example.

            regards, tom lane

--- On Tue, 1/1/08, Tom Lane <tgl@sss.pgh.pa.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
statementsshould preform the same. 

Regards,
Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:
> --- On Tue, 1/1/08, Tom Lane <tgl@sss.pgh.pa.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
statementsshould 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

--- On Wed, 1/2/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> [ 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.

Thanks for the help Tom.  This information is good to know.

Regards,
Richard Broersma Jr.