Обсуждение: 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
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
--
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