Re: delete statement returning too many results

[Ron]

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.