Hello all,
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;
However, when using the following statement, which (AFAIK) is semantically
equivalent, we see only a single row being updated/dequeued:
UPDATE queue.some_queue AS q
SET (state, awaiting) = ('executing', FALSE)
WHERE uid = (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)
RETURNING uid;
IMO the two statements should yield the same result. But, we see the first one
updating multiple rows and therefore dequeing multiple uids, yet the second one
functions as intended (ie. single item is dequeued).
We can replicate this locally in tests but I can't explain it. Is this a bug,
or am I overlooking something?
Cheers,
-Joe
PG. Postgres 10.6 in production, and the same behaviour with 10.5 + 11.2 in dev.
