pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Harmen <harmen@lijzij.de>
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 12:35:48 -0500
Message-ID: <1011786.1669743348@sss.pgh.pa.us> (raw)
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>

Harmen <harmen@lijzij.de> writes:
> 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 [...]

> 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.

Sorry, I should not have blamed SKIP LOCKED in particular; this
construction will misbehave with or without that.  The issue is with
using SELECT FOR UPDATE inside a DELETE or UPDATE that then modifies
the row that the subquery returned.  The next execution of the subquery
will, or should, return a different row: either some not-deleted row,
or the modified row.  So in this context, the result of the subquery
is volatile.  The point of putting it in a MATERIALIZED CTE is to
lock the result down regardless of that.

> 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.

You can argue that the planner should treat volatile subqueries
differently than it does today.  But the only reasonable way of
tightening the semantics would be to force re-execution of such a
subquery every time, even when it's not visibly dependent on the
outer query.  That would be pretty bad for performance, and I doubt
it would make the OP happy in this example, because what it would
mean is that his query "fails" every time not just sometimes.
(Because of that, I don't have too much trouble concluding that
the query is broken, whether or not you feel that postgres is
also broken.)

The bigger picture here is that we long ago decided that the planner
should not inquire too closely into the volatility of subqueries,
primarily because there are use-cases where people intentionally rely
on them not to be re-executed.  As an example, these queries give
different results:

regression=# select random() from generate_series(1,3);
       random        
---------------------
  0.7637195395988317
 0.09569374432524946
   0.490132093120365
(3 rows)

regression=# select (select random()) from generate_series(1,3);
       random       
--------------------
 0.9730230633436501
 0.9730230633436501
 0.9730230633436501
(3 rows)

In the second case, the sub-select is deemed to be independent
of the outer query and executed only once.  You can argue that
if that's what you want you should be forced to put the sub-select
in a materialized CTE to make that plain.  But we felt that that
would make many more people unhappy than happy, so we haven't
done it.  Maybe the question could be revisited once all PG
versions lacking the MATERIALIZED syntax are long dead. 

			regards, tom lane





view thread (8+ messages)  latest in thread

Message-ID: <1011786.1669743348@sss.pgh.pa.us>
Permalink:  ../1011786.1669743348@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1011786.1669743348@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, harmen@lijzij.de, david.g.johnston@gmail.com, ronljohnsonjr@gmail.com, gnomelver@k5d.de
  Subject: Re: delete statement returning too many results
  In-Reply-To: <1011786.1669743348@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> <20221129154653.GC15079@arp.lijzij.de> <1011786.1669743348@sss.pgh.pa.us>

* 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