Re: CTEs modifying the same table more than once

On 2016-10-01 1:37 PM, Pantelis Theodosiou wrote:
> 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.

No, the update-update and update-delete cases are explicitly *not*
caused by the same underlying implementation detail.  The update-update
case doesn't work because it internally looks like the Halloween
problem, and postgres has no way of knowing which tuples would be okay
to update twice and which ones could reintroduce the Halloween problem.

The insert-update and insert-delete cases don't work because the UPDATEs
and DELETEs have no way of seeing the INSERTed rows due to their
snapshot having been taken before the INSERT happened.  There is nothing
unpredictable about these two cases.

I don't think mentioning the insert-update and insert-delete cases in
the paragraph you quoted makes any sense.  If we want to do any changes
here (which I'm not really convinced of), I think we should add an
example of exactly this case where we currently have the two examples
demonstrating the effects of sharing the snapshot, right after the
paragraph I quoted from.


.m