Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error

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