From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: delete statement returning too many results |
Date: | 2022-11-28 14:18:45 |
Message-ID: | ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | yin.zhb@163.com | 2022-11-28 14:20:35 | Re: Re: how to implement add using upsert and trigger? |
Previous Message | Ron | 2022-11-28 14:14:22 | Re: Get table catalog from pg_indexes |