Re: CTEs modifying the same table more than once

Yes, thank you. I missed mentioning that part. It is the undelying reason
of the behaviour.

But surely it wouldn't hurt to add the 2 cases (insert-update,
insert-delete) in the last paragraph. I mean the update-delete is also
explained by the same reason.

> Trying to update the same row twice in a single statement is not
supported. Only one of the modifications takes place, but it is not easy
(and sometimes not possible) to reliably predict which one. This also
applies to deleting a row that was already updated in the same statement:
only the update is performed. Therefore you should generally avoid trying
to modify a single row twice in a single statement. In particular avoid
writing WITH sub-statements that could affect the same rows changed by the
main statement or a sibling sub-statement. The effects of such a statement
will not be predictable.

On Sat, Oct 1, 2016 at 12:22 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:

> On 2016-10-01 13:13, Pantelis Theodosiou wrote:
>
>> I answered that this is unpredictable behaviour but the docs state only
>> the
>> update-update and update-delete cases explicitly and the general wording
>> is
>> about 2 updates.
>>
>
> It's documented[1] behavior:
>
> "All the statements are executed with the same snapshot (see Chapter
> 13), so they cannot "see" one another's effects on the target tables."
>
>
> .m
>
> [1] https://www.postgresql.org/docs/current/static/queries-with.html
>