Re: Deadlock risk while inserting directly into partition?

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.

And INSERT ON CONFLICT into partitions, as we don't have nor want partitioned
indexes, for several reasons. Same for row triggers. One reason is that we
still support inheritence tables, and it's better if we can deal with both
types of child tables the same way. That neither DETACH nor NO INHERIT grammar
supports both is arguably a wart, as it requires our library to check the
relkind. Another reason is that our unique indexes are large - they're across
multiple columns, sometimes text columns, and we don't need them except to
support upsert, so they're pruned when the table is no longer "recent".

Partitions have to be manually created and dropped, so applications already
have to deal with partitions, and it's not surprising if they interact with
them in other ways, too. Partitions can themselves be partitioned, which also
need to be accessed directly.

--
Justin