On 11/28/22 07:29, Arlo Louis O'Keeffe wrote: > 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?
If I reduce your delete statement to:
DELETE FROM queue WHERE ID IN (123);
And there are 2 rows with ID 123... Should it not delete both rows?
and if I wanted a queue like behavior in that situation, I would use a cursor for update.
Then inside that cursor, use DELETE WHERE CURRENT OF?
More than one row will be deleted if there in more than one record in "queue" for the specific value of "id" (i.e "id" is not unique).