From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kirill Reshke <reshkekirill@gmail.com>
cc: Tender Wang <tndrwang@gmail.com>, Amit Langote <amitlangote09@gmail.com>,
        jian he <jian.universality@gmail.com>, exclusion@gmail.com,
        pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error
In-reply-to: <CALdSSPi6xR1tG2kLvpwNLnAjG9e0wmaY62r2_MF81ZYg5in+qQ@mail.gmail.com>
References: <19099-e05dcfa022fe553d@postgresql.org> <CACJufxF9FcuYe8XOuWLgWK77HCUHpOc6+7+NkktFFNmzw15jKg@mail.gmail.com> <CAHewXN=vF5d9O4R3+iUwLqEaP7pb8iYAN_e3vEE_p5sJHofn7w@mail.gmail.com> <2960545.1761800903@sss.pgh.pa.us> <CALdSSPi7udsgQg3PUG=Z4+-9pRg8wT3HkDvTgYvtg30xNWQ9OA@mail.gmail.com> <CALdSSPi9n2KGzKQn2Egqz3H8Nx0cgnZ8UeB5gk-KVdE3uBCj6Q@mail.gmail.com> <CA+HiwqFcejrmS_H8YB-AMB7sujB7wdJXFPdAVfDC6-19FXUjgg@mail.gmail.com> <CAHewXNmx+UXg46+WUrbPca91bmVipRTpe+SRm19GtxG6mArRhg@mail.gmail.com> <CALdSSPi6xR1tG2kLvpwNLnAjG9e0wmaY62r2_MF81ZYg5in+qQ@mail.gmail.com>
Comments: In-reply-to Kirill Reshke <reshkekirill@gmail.com>
	message dated "Thu, 30 Oct 2025 18:31:09 +0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3017910.1761832112.1@sss.pgh.pa.us>
Date: Thu, 30 Oct 2025 09:48:32 -0400
Message-ID: <3017911.1761832112@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3017911.1761832112%40sss.pgh.pa.us>
Precedence: bulk

Kirill Reshke <reshkekirill@gmail.com> writes:
> Tom wrote:
>> It's surely pretty accidental (and arguably not desirable)
>> if "DELETE FROM pt WHERE false" doesn't fail the same way.

> I cannot prove to myself why failing here is actually desirable. Can
> you elaborate?

If we throw that failure in some cases but not others, we're exposing
implementation details.

The definition could have been "throw 'cannot delete from foreign
table' only if the query actually attempts to delete some specific
row from some foreign table", but it is not implemented that way.
Instead the error is thrown during query startup if the query has
a foreign table as a potential delete target.  Thus, as things stand
today, you might or might not get the error depending on whether
the planner can prove that that partition won't be deleted from.
This is not a great user experience, because we don't (and won't)
make any hard promises about how smart the planner is.

An analogy perhaps is that whether you get a "permission denied"
error about some target table is not conditional on whether the
query actually attempts to delete any rows from it.  We go out
of our way to make sure that that happens when required by spec,
even if the planner is able to prove that no delete will happen.

None of this is meant to justify throwing an internal error here;
that's clearly bad.  I'm just saying that there would be little
wrong with fixing it by throwing "cannot delete" instead.  The user
has no right to expect that that won't happen in a case like this.

			regards, tom lane