From: Harmen <harmen@lijzij.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Ron <ronljohnsonjr@gmail.com>
Cc: Arlo Louis O'Keeffe <gnomelver@k5d.de>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: delete statement returning too many results
Date: Tue, 29 Nov 2022 16:46:53 +0100
Message-ID: <20221129154653.GC15079@arp.lijzij.de> (raw)
In-Reply-To: <632644.1669655513@sss.pgh.pa.us>
References: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de>
<ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com>
<CAKFQuwa3qYG1B-1BqRrS4L+aV0hqhjWGy4md6o_y+8qixzUJUA@mail.gmail.com>
<632644.1669655513@sss.pgh.pa.us>
On Mon, Nov 28, 2022 at 12:11:53PM -0500, Tom Lane wrote:
> So basically it's unsafe to run the sub-select more than once,
> but the query as written leaves it up to the planner whether
> to do that. I'd suggest rephrasing as
>
> WITH target_rows AS MATERIALIZED (
> SELECT id
> FROM queue
> ORDER BY id
> LIMIT 1
> FOR UPDATE
> SKIP LOCKED
> )
> DELETE FROM queue
> WHERE id IN (SELECT * FROM target_rows)
> RETURNING *;
Thanks for the explanation and suggested fix, Tom.
I'm not the original poster, but I do use similar constructions for simple
postgres queues. I've been trying for a while, but I don't understand where the
extra rows come from, or what's "silent" about SKIP LOCKED.
Because we get different results depending on the plan postgres picks, I can
see two options: either the query is broken, or postgres is broken. Assuming it's
the former, would there be a way to make it clearer that the "obvious" (to me)
way to use SKIP LOCKED is wrong?
Thanks!
Harmen
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: harmen@lijzij.de, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, ronljohnsonjr@gmail.com, gnomelver@k5d.de
Subject: Re: delete statement returning too many results
In-Reply-To: <20221129154653.GC15079@arp.lijzij.de>
References: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de> <ef3df301-3488-ea3e-7eab-4b97a987d2d7@gmail.com> <CAKFQuwa3qYG1B-1BqRrS4L+aV0hqhjWGy4md6o_y+8qixzUJUA@mail.gmail.com> <632644.1669655513@sss.pgh.pa.us> <20221129154653.GC15079@arp.lijzij.de>
* 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