Re: "with recursive" ignores side effects?

Fabien COELHO-3 wrote
> The key issue for me is that table Foo is updated (as shown by the last
> column), but although 'two' was updated to '*' by iteration 1, the last
> iteration still sees the initial 'two' which does not exist anymore.
>
> Am I wrong somewhere? Or is this a subtle bug?

My recollection is that a single statement, including its WITH clauses,
always sees whatever database state was in effect at the beginning of the
statement's execution. Updates to the database during the statement are not
visible until the statement has completed.

Note that with the introduction of updateable CTE the use of the "RETURNING"
clause is needed to allow other portions of the same query to "see" the
changes made by the DDL statement.

Read section 7.8.2 in detail as you are basically implementing this when you
perform data update statements inside a function called using "SELECT".

I'm not sure I can explain this any better, nor am I certain the
documentation is as clear as it could be, but I am pretty certain the
behavior itself is intended.

A statement is not a procedure but a declaration of what you need to have
done. Recursion (iteration) allows you to more easily handle/walk
hierarchical data but said hierarchy remains static for the duration of the
query.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/with-recursive-ignores-side-effects-tp5766555p5766565.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.