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.
Date: Tue, 06 Sep 2022 10:31:48 -0400
Message-ID: <3160310.1662474708@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHtNzahx=5mCG0+jxWL1hBxhZqcG_NCHsCvy6nQG8itnLeyYyQ@mail.gmail.com>
References: <CAHtNzahx=5mCG0+jxWL1hBxhZqcG_NCHsCvy6nQG8itnLeyYyQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, wilcokruijer@gmail.com, 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: <3160310.1662474708@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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