Re: NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.

Wilco Kruijer <wilcokruijer(at)gmail(dot)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