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?
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).
-- Angular momentum makes the world go 'round.
