Re: Strange behavior of insert CTE with trigger

On Fri, Mar 27, 2015 at 4:18 PM, Anil Menon <gakmenon(at)gmail(dot)com> wrote:

> Hi,
>
> I am trying to wrap my head around a strange problem I am having. I have
> double checked the documentation but I could not find anything on this.
>
> ​[...]​

>
> However I get no rows returned from the select statement- looks the
> insert to abc_Excp_log is executed *after* the select statement or some
> sort of race condition is executed.
>
> Is this documented anywhere and is the expected behavior? Documented
> anywhere? The CTE part of the PG doc does not say anything on this.
>
>
​Yes, it does:
http://www.postgresql.org/docs/9.3/static/queries-with.html#QUERIES-WITH-MODIFYING

​Specifically (nearly the entire last 1/6 of the page - the "Data-Modifying
Statements in WITH" section):

​"​
The sub-statements in WITH are executed concurrently with each other and
with the main query.
"

Since you cannot see even the updated price on products in the following
query the fact that you cannot see the result of triggers on the same is a
logical conclusion even though triggers are not explicitly mentioned.

WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;

David J.