List partitioning helper can cause deadlocks if the table to partition has foreign keys
In gitlab-com/gl-infra/production#7907 (closed), while trying to convert ci_builds_metadata to a partitioned table, the ALTER TABLE ATTACH PARTITION
statement encountered a deadlock trying to acquire an AccessExclusiveLock
on ci_builds
.
That partition attachment statement takes an AccessExclusiveLock
on the table to attach, and additionally each table that it references by foreign key.
Other application code routinely locks ci_builds
followed by ci_builds_metadata
. As a result, a deadlock occurred.
Change the partitioning helper to allow specifying a locking order, so that a deadlock is less likely.