Thread: CTEs modifying the same table more than once
I saw a question at a StackOverflow site:
http://dba.stackexchange.com/questions/151199/why-cant-rows-inserted-in-a-cte-be-updated
regarding statements with CTEs that mofify a table twice, with an insert and then an update:http://dba.stackexchange.com/questions/151199/why-cant-rows-inserted-in-a-cte-be-updated
(post:)
---------------------------------------------------------------------------
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.In PostgreSQL 9.5, given a simple table created with:
create table tbl ( id serial primary key, val integer
);I run SQL to INSERT a value, then UPDATE it in the same statement:
WITH newval AS ( INSERT INTO tbl(val) VALUES (1) RETURNING id
) UPDATE tbl SET val=2 FROM newval WHERE tbl.id=newval.id;The result is that the UPDATE is ignored:
testdb=> select * from tbl;
┌────┬─────┐
│ id │ val │
├────┼─────┤
│ 1 │ 1 │
└────┴─────┘
(1 row)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
Yes, thank you. I missed mentioning that part. It is the undelying reason of the behaviour.
> 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@joh.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
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