pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedRETURNING does not explain evaluation context for subqueries
2+ messages / 2 participants
[nested] [flat]
* RETURNING does not explain evaluation context for subqueries
@ 2020-02-05 16:32 PG Doc comments form <noreply@postgresql.org>
2020-03-14 01:41 ` Re: RETURNING does not explain evaluation context for subqueries Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2020-02-05 16:32 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: deonjtan@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/dml-returning.html
Description:
In the docs explaining RETURNING
https://www.postgresql.org/docs/11/dml-returning.html there is no mention of
the fact that a nested sub-select in the RETURNING statement executes on the
table as if the INSERT/UPDATE had not happened.
I suppose maybe this might be obvious if you understand how SQL works but I
think it is nuanced enough that it is worth explaining here as it provides
some useful features for UPSERT queries. Example:
```sql
create table foo (x int primary key, y int);
--=> CREATE TABLE
insert into foo (x, y) values (1, 1);
--=> INSERT 0 1
update foo set y = 2 where x = 1 returning (select y from foo where x = 1)
as old_y;
/* =>
* old_y
* -------
* 1
* (1 row)
*
* UPDATE 1
*/
select * from foo;
/* =>
* x | y
* ---+---
* 1 | 2
* (1 row)
*/
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RETURNING does not explain evaluation context for subqueries
2020-02-05 16:32 RETURNING does not explain evaluation context for subqueries PG Doc comments form <noreply@postgresql.org>
@ 2020-03-14 01:41 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2020-03-14 01:41 UTC (permalink / raw)
To: deonjtan@gmail.com, PostgreSQL-development <pgsql-hackers@postgresql.org>
On Wed, Feb 5, 2020 at 04:32:45PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/11/dml-returning.html
> Description:
>
> In the docs explaining RETURNING
> https://www.postgresql.org/docs/11/dml-returning.html there is no mention of
> the fact that a nested sub-select in the RETURNING statement executes on the
> table as if the INSERT/UPDATE had not happened.
>
> I suppose maybe this might be obvious if you understand how SQL works but I
> think it is nuanced enough that it is worth explaining here as it provides
> some useful features for UPSERT queries. Example:
>
> ```sql
> create table foo (x int primary key, y int);
> --=> CREATE TABLE
> insert into foo (x, y) values (1, 1);
> --=> INSERT 0 1
> update foo set y = 2 where x = 1 returning (select y from foo where x = 1)
> as old_y;
> /* =>
> * old_y
> * -------
> * 1
> * (1 row)
> *
> * UPDATE 1
> */
> select * from foo;
> /* =>
> * x | y
> * ---+---
> * 1 | 2
> * (1 row)
> */
> ```
Sorry for the delay in replying. I am moving this thread to hackers
because it isn't clearly a documentation issue. I did some research on
this and it is kind of confusing:
CREATE TABLE foo (x INT PRIMARY KEY, y INT);
INSERT INTO foo (x, y) VALUES (1, 1);
UPDATE foo SET y = y + 1 WHERE x = 1 RETURNING y;
y
---
2
SELECT y FROM foo;
y
---
2
UPDATE foo SET y = y + 1 WHERE x = 1 RETURNING (y);
y
---
3
SELECT y FROM foo;
y
---
3
UPDATE foo SET y = y + 1 WHERE x = 1 RETURNING (SELECT y);
y
---
4
SELECT y FROM foo;
y
---
4
UPDATE foo SET y = y + 1 WHERE x = 1 RETURNING (SELECT y FROM foo);
y
---
4
SELECT y FROM foo;
y
---
5
So, it is only when querying 'foo' that it uses the pre-UPDATE
visibility snapshot. So the 'y' in 'SELECT y' is the 'y' from the
update, but the 'y' from 'SELECT y FROM foo' uses the snapshot from
before the update. My guess is that we just didn't consider the rules
for what the 'y' references, and I bet if I dig into the code I can find
out why this happening.
RETURNING for INSERT/UPDATE/DELETE isn't part of the SQL standard, so we
don't have much guidance there. It is as though the 'FROM foo' changes
the resolution of the 'y' because it is closer.
I am unclear if this should be documented or changed, or neither.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-03-14 01:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-05 16:32 RETURNING does not explain evaluation context for subqueries PG Doc comments form <noreply@postgresql.org>
2020-03-14 01:41 ` Bruce Momjian <bruce@momjian.us>
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