From: | Kirk Wolak <wolakk(at)gmail(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: delete statement returning too many results |
Date: | 2022-11-29 22:16:23 |
Message-ID: | CACLU5mT1RRzev-c5cYEXdW6s5=-jyA6PGFxRD1LzwNANyjdG_A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Nov 28, 2022 at 9:18 AM Ron <ronljohnsonjr(at)gmail(dot)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?
>
If I reduce your delete statement to:
DELETE FROM queue WHERE ID IN (123);
And there are 2 rows with ID 123... Should it not delete both rows?
and if I wanted a queue like behavior in that situation, I would use a
cursor for update.
Then inside that cursor, use DELETE WHERE CURRENT OF?
> 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 | shashidhar Reddy | 2022-11-30 00:38:39 | Re: plpgsql_check_function issue after upgrade |
Previous Message | Ivan Panchenko | 2022-11-29 21:22:12 | Re: postgresql 13.1: precision of spatial operations |