pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedCTEs modifying the same table more than once
4+ messages / 2 participants
[nested] [flat]
* CTEs modifying the same table more than once
@ 2016-10-01 11:13 Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pantelis Theodosiou @ 2016-10-01 11:13 UTC (permalink / raw)
To: pgsql-docs
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:
(post:)
---------------------------------------------------------------------------
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)
---------------------------------------------------------------------------
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.
I suggest that the insert-update and insert-delete cases are added as well
(assuming that my understanding is correct and that these also result in
unpredictable results).
Pantelis Theodosiou
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CTEs modifying the same table more than once
@ 2016-10-01 11:22 Marko Tiikkaja <marko@joh.to>
parent: Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Marko Tiikkaja @ 2016-10-01 11:22 UTC (permalink / raw)
To: Pantelis Theodosiou <ypercube@gmail.com>; pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CTEs modifying the same table more than once
@ 2016-10-01 11:37 Pantelis Theodosiou <ypercube@gmail.com>
parent: Marko Tiikkaja <marko@joh.to>
0 siblings, 1 reply; 4+ messages in thread
From: Pantelis Theodosiou @ 2016-10-01 11:37 UTC (permalink / raw)
To: pgsql-docs
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@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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CTEs modifying the same table more than once
@ 2016-10-01 12:07 Marko Tiikkaja <marko@joh.to>
parent: Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Marko Tiikkaja @ 2016-10-01 12:07 UTC (permalink / raw)
To: Pantelis Theodosiou <ypercube@gmail.com>; pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-10-01 12:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-01 11:13 CTEs modifying the same table more than once Pantelis Theodosiou <ypercube@gmail.com>
2016-10-01 11:22 ` Marko Tiikkaja <marko@joh.to>
2016-10-01 11:37 ` Pantelis Theodosiou <ypercube@gmail.com>
2016-10-01 12:07 ` Marko Tiikkaja <marko@joh.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox