Re: Deadlock risk while inserting directly into partition?

On Thu, 24 Jun 2021 at 10:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > I've recently been thinking it would be good if you were unable to
> > access partitions directly by name at all.
>
> I strongly disagree. That's essentially betting the whole farm on
> our always being able to optimize parent-level operations fully,
> which I do not think we are anywhere close to.

Did you have anything in particular in mind here? I thought we got
all these in 8edd0e794. I think the one that was missing was parallel
index scans. That commit adds code to add the missing paths in
add_paths_to_append_rel().

As of 14, UPDATE/DELETEs when a single partition remains after pruning
should be fairly comparable to a direct UPDATE/DELETE on the
partition.

Certainly, back when partitioning was added there were still lots of
use cases for querying partitions directly, but as far as I see it,
there's not many of those left. The patch in [1] aims to reduce the
overhead of one of these. I have a patch locally for another one. I'm
currently not aware of any other cases where querying a single
partition is slow.

But... maybe there are some cases where a user can be certain that all
interesting records are contained in a single partition but
partitioning pruning cannot prove it...So maybe what you say is right.
The workaround there would be to add a qual that allows pruning to
work.

David

[1] https://www.postgresql.org/message-id/CA+HiwqGqh-aHXGO8-_ftU7e2GdGUr_T-xqr6Z_6uagyJpEpJfA@mail.gmail.com