with and trigger

On Wed, 29 May 2019 at 12:52, PegoraroF10 <marcos@f10.com.br> wrote:

This trigger will not work because Master record was not inserted yet.

That seems reasonable. Since the transaction is meant to be atomic any
select within the query should return data from tables as they are at the
start of the transaction, the insert won't happen (as far as the rest of
the query is concerned) until it commits.

Or have I misunderstood what you're saying?

Geoff

Em qua, 29 de mai de 2019 às 08:52, PegoraroF10 <marcos@f10.com.br>
escreveu:

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.

To see updated data using a trigger in the same transaction you should
create trigger on AFTER event.

Regards,

--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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