MIME-Version: 1.0
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> <3017911.1761832112@sss.pgh.pa.us>
In-Reply-To: <3017911.1761832112@sss.pgh.pa.us>
From: Amit Langote <amitlangote09@gmail.com>
Date: Fri, 31 Oct 2025 09:30:39 +0900
Message-ID: <CA+HiwqH-qT-AMVyKpe6=Ph-_6=UnHaPiO4YjyzLGiq3TW7KU=Q@mail.gmail.com>
Subject: Re: BUG #19099: Conditional DELETE from partitioned table with
 non-updatable partition raises internal error
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kirill Reshke <reshkekirill@gmail.com>, Tender Wang <tndrwang@gmail.com>, 
	jian he <jian.universality@gmail.com>, exclusion@gmail.com, 
	pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BHiwqH-qT-AMVyKpe6%3DPh-_6%3DUnHaPiO4YjyzLGiq3TW7KU%3DQ%40mail.gmail.com>
Precedence: bulk

On Thu, Oct 30, 2025 at 10:48=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote=
:
> 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.

We might be able to throw the "cannot delete from foreign table" like this:

@@ -987,6 +987,16 @@ add_row_identity_columns(PlannerInfo *root, Index rtin=
dex,

         fdwroutine =3D GetFdwRoutineForRelation(target_relation, false);

+        if (fdwroutine->ExecForeignDelete =3D=3D NULL)
+            ereport(ERROR,
+                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                     errmsg("cannot delete from foreign table \"%s\"",
+                            RelationGetRelationName(target_relation))));
+        if (fdwroutine->ExecForeignUpdate =3D=3D NULL)
+            ereport(ERROR,
+                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                     errmsg("cannot update foreign table \"%s\"",
+                            RelationGetRelationName(target_relation))));
         if (fdwroutine->AddForeignUpdateTargets !=3D NULL)
             fdwroutine->AddForeignUpdateTargets(root, rtindex,
                                                 target_rte, target_relatio=
n);

but I am not sure how consistent the following is after applying that:

postgres=3D# set enable_partition_pruning to off;
SET
postgres=3D# EXPLAIN verbose DELETE FROM pt WHERE false;
ERROR:  cannot delete from foreign table "p1"
postgres=3D# set enable_partition_pruning to on;
SET

-- we don't even hit the foreign table in the planner
postgres=3D# EXPLAIN verbose DELETE FROM pt WHERE false;
                      QUERY PLAN
-------------------------------------------------------
 Delete on public.pt  (cost=3D0.00..0.00 rows=3D0 width=3D0)
   ->  Result  (cost=3D0.00..0.00 rows=3D0 width=3D0)
         Output: ctid
         Replaces: Scan on pt
         One-Time Filter: false
(5 rows)

--=20
Thanks, Amit Langote