Re: Deadlock risk while inserting directly into partition?

On Mon, 2021-06-28 at 23:46 +1200, David Rowley wrote:
> On Sat, 26 Jun 2021 at 16:41, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> > On Thu, Jun 24, 2021 at 10:27:06AM +1200, David Rowley wrote:
> > > I think the reasons for doing operations directly on partitions are
> > > being reduced with each release. What operations do people really
> > > need to do on partitions now? TRUNCATE is probably one, maybe there's
> > > still a need to CREATE INDEX.
> >
> > We always SELECT out of parent tables, but need to be able to CREATE INDEX on
> > partitions.
>
> I imagined we'd have something along the lines of: ALTER TABLE
> partitioned_table ALTER PARTITION part CREATE INDEX. I admit I don't
> know how that would look when faced with multi-level partitioning.

Some general comments on this initiative:

I like it that partitions are normal tables in PostgreSQL, and that I
can just use them in SQL statements.

If there is really no other way to avoid certain problems, we can change
that, but I would greatly prefer if it remain the way it is now.
Perhaps we can document such deadlock risks, or we can find a ways to
avoid them.

I think reducing functionality should be the last route to consider.
If we introduce new syntax to access partitions, we will end up with a lot
of new syntax, and we might well have an endless stream of requests for
ways to do X with a partition.

Yours,
Laurenz Albe