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: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 31 Oct 2025 14:50:15 +1300
Message-ID: <CAApHDvpYEqJ6h-3NWi_4S19RY9NARpJ3h8CRmWYbz5MJFqE-sg@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>, 
	Amit Langote <amitlangote09@gmail.com>, jian he <jian.universality@gmail.com>, 
	exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpYEqJ6h-3NWi_4S19RY9NARpJ3h8CRmWYbz5MJFqE-sg%40mail.gmail.com>
Precedence: bulk

On Fri, 31 Oct 2025 at 02:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

It's a good point, but I doubt we could change this fact as I expect
there are people relying on pruned partitions being excluded from this
check. It seems reasonable that someone might want to do something
like archive ancient time-based partitioned table partitions into
file_fdw stored on a compressed filesystem so that they can still at
least query old data should they need to.  If we were to precheck that
all partitions support an UPDATE/DELETE, then it could break workloads
that do updates on recent data in heap-based partitions. Things would
go bad for those people if they switched off partition pruning, but I
doubt that would be the only reason as that would also add a huge
amount of overhead to their SELECT statements.

In any case, the planner is now very efficient at not loading any
metadata for pruned partitions, so I don't see how we'd do this
without adding possibly large overhead to the planner. I'd say we're
well beyond the point of being able to change this now.

David