Re: Strange behavior of insert CTE with trigger

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, Mar 27, 2015 at 4:18 PM, Anil Menon <gakmenon@xxxxxxxxx> 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.