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 1ozhfp-00017W-TR for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 17:12:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ozhfo-0004Mw-Q3 for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 17:12:04 +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 1ozhfo-0004Mm-F5 for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 17:12:04 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ozhfm-00078e-Hg for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 17:12:04 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 2ASHBr1o632645; Mon, 28 Nov 2022 12:11:53 -0500 From: Tom Lane To: "David G. Johnston" cc: Ron , "Arlo Louis O'Keeffe" , pgsql-general@lists.postgresql.org Subject: Re: delete statement returning too many results In-reply-to: References: <34484222-02BB-408A-8FBB-DCE0F64D96B5@k5d.de> Comments: In-reply-to "David G. Johnston" message dated "Mon, 28 Nov 2022 07:34:45 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <632643.1669655513.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 28 Nov 2022 12:11:53 -0500 Message-ID: <632644.1669655513@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "David G. Johnston" writes: > There is a nice big caution regarding the default read committed isolati= on > mode, order by, and for update, in the documentation, but I cannot work = out > exactly why this example seems to be triggering it. The is talking about a rather different scenario. I managed to reproduce this locally. I find that initially, with an empty queue table, you get a query plan like Delete on queue (cost=3D0.38..8.42 rows=3D1 width=3D38) -> Nested Loop (cost=3D0.38..8.42 rows=3D1 width=3D38) -> HashAggregate (cost=3D0.23..0.24 rows=3D1 width=3D40) Group Key: "ANY_subquery".id -> Subquery Scan on "ANY_subquery" (cost=3D0.15..0.22 row= s=3D1 width=3D40) -> Limit (cost=3D0.15..0.21 rows=3D1 width=3D14) -> LockRows (cost=3D0.15..74.15 rows=3D1200 w= idth=3D14) -> Index Scan using queue_pkey on queue = queue_1 (cost=3D0.15..62.15 rows=3D1200 width=3D14) -> Index Scan using queue_pkey on queue (cost=3D0.15..8.17 rows= =3D1 width=3D14) Index Cond: (id =3D "ANY_subquery".id) which is fine because the LockRows bit will be run only once. However, after the table's been stomped on for awhile (and probably not till after autovacuum runs), that switches to Delete on queue (cost=3D0.25..16.31 rows=3D1 width=3D38) -> Nested Loop Semi Join (cost=3D0.25..16.31 rows=3D1 width=3D38) Join Filter: (queue.id =3D "ANY_subquery".id) -> Index Scan using queue_pkey on queue (cost=3D0.12..8.14 rows= =3D1 width=3D14) -> Subquery Scan on "ANY_subquery" (cost=3D0.12..8.16 rows=3D1 = width=3D40) -> Limit (cost=3D0.12..8.15 rows=3D1 width=3D14) -> LockRows (cost=3D0.12..8.15 rows=3D1 width=3D14) -> Index Scan using queue_pkey on queue queue_= 1 (cost=3D0.12..8.14 rows=3D1 width=3D14) and then you start to get failures, because each re-execution of the subquery produces a fresh row thanks to the silent SKIP LOCKED. 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 *; regards, tom lane