Re: RETURNING does not explain evaluation context for subqueries

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(at)momjian(dot)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 +