Thread: with and trigger

We like to use With to insert, update and return some value to user. But some
informations of those related tables are not available on that time, is that
a bug ?

with
  Master(Master_ID) as (insert into Master(Customer_ID, Field2) values(1,
'BlaBla') returning Master_ID),
  Detail as (insert into Detail(Master_ID, Product_ID, ProductValue) select
Master_ID, 5, 50 from Master)
select Master_ID from Master;

This code works but not as expected because we have a trigger which does not
see that data yet.
Suppose a trigger on Detail which needs to find any info from a table which
was inserted on this With. That info is not available, like ...
create function DetailOfDetail() returns trigger() as -- this trigger
function is before insert on Detail
begin
  new.Discount = (select discount from Customer inner join Master
using(Customer_ID) where Master_ID = new.Master_ID)
end;
This trigger will not work because Master record was not inserted yet.

If change it to a DO it would work but we would like that result, so ...
This trigger is obviously an example, our tables have more complex
structures but here we want only to understand the way postgres works or if
it´s not working properly.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Well, I think is not a transaction problem, because if you do the same thing
on a DO it will work. 
DO $$
  declare vMaster_ID integer;
begin
  insert into Master(Customer_ID, Field2) values(1, 'BlaBla') returning
Master_ID into vMaster_ID;
  insert into Detail(Master_ID, Product_ID, ProductValue) values(vMaster_ID,
5, 50);
end $$

As you can see, works exactly the same way and Detail trigger works as
expected, why ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

PegoraroF10 <marcos@f10.com.br> writes:
> We like to use With to insert, update and return some value to user. But some
> informations of those related tables are not available on that time, is that
> a bug ?

No, see the "WITH Clause" section of the SELECT reference page:

    The primary query and the WITH queries are all (notionally) executed
    at the same time. This implies that the effects of a data-modifying
    statement in WITH cannot be seen from other parts of the query, other
    than by reading its RETURNING output. If two such data-modifying
    statements attempt to modify the same row, the results are
    unspecified.

This doesn't explicitly talk about triggers, but I think our attitude
about the case you're discussing is that the results are unspecified.
If a trigger fired in one WITH arm tries to look at the table(s)
modified by other WITH arms, it might or might not see those changes.

            regards, tom lane

On 5/29/19 8:26 AM, Tom Lane wrote:
> PegoraroF10 <marcos@f10.com.br> writes:
>> We like to use With to insert, update and return some value to user. But some
>> informations of those related tables are not available on that time, is that
>> a bug ?
> No, see the "WITH Clause" section of the SELECT reference page:
>
>      The primary query and the WITH queries are all (notionally) executed
>      at the same time. This implies that the effects of a data-modifying
>      statement in WITH cannot be seen from other parts of the query, other
>      than by reading its RETURNING output. If two such data-modifying
>      statements attempt to modify the same row, the results are
>      unspecified.
>
> This doesn't explicitly talk about triggers, but I think our attitude
> about the case you're discussing is that the results are unspecified.
> If a trigger fired in one WITH arm tries to look at the table(s)
> modified by other WITH arms, it might or might not see those changes.

Are CTEs still optimization fences?
https://www.2ndquadrant.com/en/blog/postgresql-ctes-are-optimization-fences/

-- 
Angular momentum makes the world go 'round.

>
>Are CTEs still optimization fences?
>https://www.2ndquadrant.com/en/blog/postgresql-ctes-are-optimization-fences/

Yes, but not in 12.


Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company

Ron <ronljohnsonjr@gmail.com> writes:
> On 5/29/19 8:26 AM, Tom Lane wrote:
>> This doesn't explicitly talk about triggers, but I think our attitude
>> about the case you're discussing is that the results are unspecified.
>> If a trigger fired in one WITH arm tries to look at the table(s)
>> modified by other WITH arms, it might or might not see those changes.

> Are CTEs still optimization fences?
> https://www.2ndquadrant.com/en/blog/postgresql-ctes-are-optimization-fences/

Yes, if they contain data-modifying statements ... but that's not
relevant to the point at hand, which is that the run-time behavior
is not specified.

            regards, tom lane