pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: delete statement returning too many results
Date: Mon, 28 Nov 2022 08:18:45 -0600
Message-ID: <ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com> (raw)
In-Reply-To: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de>
References: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de>

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.





view thread (8+ messages)  latest in thread

Message-ID: <ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com>
Permalink:  ../ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com/
Also on:    postgresql.org/message-id/ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: delete statement returning too many results
  In-Reply-To: <ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com>
  References: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de> <ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox