Re: with and trigger

On 5/29/19 8:26 AM, Tom Lane wrote:
> PegoraroF10 <marcos(at)f10(dot)com(dot)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.