From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wilco Kruijer <wilcokruijer@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: NULL returned when using RETURNING in main query in combination
 with a CTE containing FOR UPDATE.
In-reply-to: 
 <CAHtNzahx=5mCG0+jxWL1hBxhZqcG_NCHsCvy6nQG8itnLeyYyQ@mail.gmail.com>
References: 
 <CAHtNzahx=5mCG0+jxWL1hBxhZqcG_NCHsCvy6nQG8itnLeyYyQ@mail.gmail.com>
Comments: In-reply-to Wilco Kruijer <wilcokruijer@gmail.com>
	message dated "Tue, 06 Sep 2022 12:28:59 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3160309.1662474708.1@sss.pgh.pa.us>
Date: Tue, 06 Sep 2022 10:31:48 -0400
Message-ID: <3160310.1662474708@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3160310.1662474708%40sss.pgh.pa.us>
Precedence: bulk

Wilco Kruijer <wilcokruijer@gmail.com> writes:
> wilco=> with before as (
>   select balance from acc_balance where acc_name = 'Wilco' FOR UPDATE
> )
> update acc_balance set balance = 300 where acc_name = 'Wilco'
> returning balance - (select balance from before) as delta, (select balance
> from before limit 1) as before, balance;

You seem to be assuming that the WITH query will run before the UPDATE
happens.  As formulated, it will not, because the UPDATE proper
does not use its value.  So we don't get around to executing it until
the RETURNING clause demands its value.  I don't recall exactly why
FOR UPDATE causes the already-modified row to not be visible, but
if it were visible you'd get the updated balance not the original.
So you really want no-FOR-UPDATE semantics here, to see the balance
from before the query started.

			regards, tom lane