Thread: "with recursive" ignores side effects?

I have an unexpected issue with a side effects on a table which is ignored
by a recursive query. I've tried to simplify the issue to a simple
example, see the attached sql script.

Version 1 results in:

   it / fid / stuff / foo full contents
   0 | 0 |       | {1:one,2:two}
   1 | 1 | one   | {1:one,2:*}
   2 | 2 | two   | {1:*,2:*}

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.

Version 2 illustrates more or less the behavior I would have expected.
I've read again Section 7.8 about WITH queries, but I have not seen
anything that would disprove my expectation.

Am I wrong somewhere? Or is this a subtle bug?

--
Fabien.

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.