On Mon, Nov 28, 2022 at 7:18 AM Ron <ronljohnso...@gmail.com> wrote: > 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). > > Given that the example code provided has "ID" as a PK on the queue table this fact, while true, is unhelpful for this specific question.
There is a nice big caution regarding the default read committed isolation mode, order by, and for update, in the documentation, but I cannot work out exactly why this example seems to be triggering it. https://www.postgresql.org/docs/current/sql-select.html David J.
