On Wed, 23 Jun 2021 at 21:07, Amit Kapila <amit.kapila16@gmail.com> wrote:
> I noticed that while inserting directly into a partition table we
> compute the PartitionCheckExpr by traversing all the parent partitions
> via ExecPartitionCheck()->RelationGetPartitionQual()->generate_partition_qual().
> We take AccessShareLock on parent tables while generating qual.
>
> Now, on the other hand, while dropping constraint on a partitioned
> table, we take the lock from parent to all the child tables.
>
> I think taking locks in opposite directions can lead to deadlock in
> these operations.
I wonder if it's possible to do any better here? Surely when
traversing from child to parent we must lock the child before checking
what the parent relation is.
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. There's not much to gain performance
wise now inserting directly into a partition. There's a pending patch
around that aims to speed that up further by caching the last used
partition and trying that first.
I've recently been thinking it would be good if you were unable to
access partitions directly by name at all. That would also get around
the problem of having to lock all non-pruned partitions during queries
to the partitioned table. Maybe it's too late for that though.
David