Reduce database lock acquired by the partition manager

Currently, when adding a partition, the partition manager runs a query of the form

CREATE TABLE new_partition PARTITION OF parent FOR VALUES <partition-spec>

and when detaching a partition, a query of the form

ALTER TABLE parent DETACH PARTITION old_partition;

Both of these take a short-duration ACCESS EXCLUSIVE lock on the table. We run these queries with lock retries, but for extremely high traffic tables this can still cause lock contention.

Postgres supports a different way to create and detach partitions, taking only a SHARE UPDATE EXCLUSIVE lock (which blocks writes, but not reads).

To create a new partition, we could instead run

CREATE TABLE new_partition (LIKE parent INCLUDING ALL); -- Doesn't take any lock
ALTER TABLE parent ATTACH PARTITION new_partition FOR VALUES <partition-spec>; -- Takes SHARE UPDATE EXCLUSIVE

And likewise to detach a partition,

ALTER TABLE parent DETACH PARTITION old_partition CONCURRENTLY;

The concurrent detach version may be more complex as it still acquires a SHARE UPDATE EXCLUSIVE lock but cannot be run in a transaction, so it can't use the lock retries mechanism.

We should switch the partition manager to use these more efficient locking constructs.

Deliverables

1. Partition manager takes only a SHARE UPDATE EXCLUSIVE lock during partition creation
2. Decide if reduced locking during partition detach is worth the tradeoff of not using lock retries.
3. If in 2 we decide to reduce the locking level for partition detach, partition manager only takes a SHARE UPDATE EXCLUSIVE lock during partition detach.