Joe Wildish <joe-postgresql.org@xxxxxxxxxx> writes:
> We are seeing an inexplicable behaviour when issuing an "UPDATE..RETURNING" statement. I am unsure if it is a Postgres bug. Additional eyes-on would be much appreicated.
> When issuing the following statement we are seeing multiple rows UPDATE'd despite the use of LIMIT 1 and despite the "uid" column in the "some_queue" table having a PRIMARY KEY constraint on it:
> UPDATE queue.some_queue AS q
> SET (state, awaiting) = ('executing', FALSE)
> FROM (SELECT uid
> FROM queue.some_queue
> WHERE awaiting
> AND process_after <= CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
> ORDER BY process_after ASC
> FOR UPDATE SKIP LOCKED
> LIMIT 1)
> AS dq(uid)
> WHERE q.uid = dq.uid
> RETURNING q.uid;
Yeah, there was another similar complaint a few weeks ago --- has this
suddenly gotten to be a popular coding idea?
The basic problem with what you have here is that FOR UPDATE (especially
with SKIP LOCKED) makes the sub-select's output unstable by definition.
If it's executed more than once then you might get different rows back,
allowing the outer UPDATE's join to potentially match multiple rows from
the outer instance of queue.some_queue. Typically, since it's LIMIT 1,
I'd think that the planner would put dq on the outside of a nestloop plan
and you'd escape seeing any problem --- but if it gets put on the inside
of a nestloop, it's going to misbehave.
There are (at least) two different ways that the sub-select's output
might change when re-executed, even though it's still using the same
snapshot as before:
1. If some other transaction releases a row lock in between, SKIP LOCKED
might not skip that row any more.
2. The row returned the first time will absolutely not get chosen the
second time, given this particular query formulation, because its latest
updated version will have awaiting = false thanks to the action of the
outer UPDATE, so it'll fail the inner WHERE test.
The way I'd recommend fixing it is to put the FOR UPDATE into a WITH
to guarantee single execution:
WITH dq(uid) AS (SELECT uid ... LIMIT 1)
UPDATE queue.some_queue q SET ...
FROM dq
WHERE q.uid = dq.uid
RETURNING q.uid;
regards, tom lane
