Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p038e-0006xX-Tu for pgsql-general@arkaria.postgresql.org; Tue, 29 Nov 2022 16:07:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p038d-00027I-Qx for pgsql-general@arkaria.postgresql.org; Tue, 29 Nov 2022 16:07:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p02p3-000828-IK for pgsql-general@lists.postgresql.org; Tue, 29 Nov 2022 15:47:01 +0000 Received: from arp.lijzij.de ([185.139.33.98]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p02oy-0003JP-F6 for pgsql-general@lists.postgresql.org; Tue, 29 Nov 2022 15:47:01 +0000 Received: from localhost (arp.lijzij.de [local]) by arp.lijzij.de (OpenSMTPD) with ESMTPA id 0d36aa26; Tue, 29 Nov 2022 16:46:53 +0100 (CET) Date: Tue, 29 Nov 2022 16:46:53 +0100 From: Harmen To: Tom Lane Cc: "David G. Johnston" , Ron , Arlo Louis O'Keeffe , pgsql-general@lists.postgresql.org Subject: Re: delete statement returning too many results Message-ID: <20221129154653.GC15079@arp.lijzij.de> References: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de> <632644.1669655513@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <632644.1669655513@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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