On Mon, Nov 28, 2022 at 7:18 AM Ron <ronljohnsonjr@xxxxxxxxx> 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.
David J.
