Hello everyone,
I am seeing weird behaviour of a delete statement that is returning more results than I am expecting.
This is the query:
DELETE FROM queue
WHERE
id IN (
SELECT id
FROM queue
ORDER BY id
LIMIT 1
FOR UPDATE
SKIP LOCKED
)
RETURNING *;
My understanding is that the limit in the sub-select should prevent this query from ever
returning more than one result. Sadly I am seeing cases where there is more than one result.
This repository has a Java setup that pretty reliably reproduces my issue:
https://github.com/ArloL/postgres-query-error-demo
I checked the docs for select and delete and couldn’t find any hint for cases
where the behaviour of limit might be surprising.
Am I missing something?
Thanks,
Arlo