Use access exclusive lock for FKs on partitioned tables

What does this MR do and why?

Adding a foreign key on a partitioned table needs an AccessExclusiveLock on the target table. In this case, when the reverse_lock_order option is specified, we'll be using AccessExclusiveLock instead of ShareRowExclusiveLock.

With non-partitioned tables:

gitlabhq_test_ci=# begin;
BEGIN
gitlabhq_test_ci=#  ALTER TABLE ci_builds_metadata ADD CONSTRAINT fk_e20479742e_p FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds (partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
ALTER TABLE
gitlabhq_test_ci=# select locktype, mode, granted, relation::regclass from pg_locks;
   locktype    |         mode          | granted |      relation
---------------+-----------------------+---------+--------------------
 relation      | AccessShareLock       | t       | pg_locks
 virtualxid    | ExclusiveLock         | t       |
 relation      | AccessShareLock       | t       | ci_builds
 relation      | ShareRowExclusiveLock | t       | ci_builds
 transactionid | ExclusiveLock         | t       |
 relation      | AccessShareLock       | t       | ci_builds_metadata
 relation      | ShareRowExclusiveLock | t       | ci_builds_metadata
(7 rows)

gitlabhq_test_ci=# rollback;
ROLLBACK

With partitioned tables:

gitlabhq_test_ci=# begin;
BEGIN
gitlabhq_test_ci=#  ALTER TABLE p_ci_builds_metadata ADD CONSTRAINT fk_e20479742e_p FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds (partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE
gitlabhq_test_ci=# select locktype, mode, granted, relation::regclass from pg_locks;
   locktype    |         mode          | granted |       relation
---------------+-----------------------+---------+----------------------
 relation      | AccessShareLock       | t       | pg_locks
 virtualxid    | ExclusiveLock         | t       |
 relation      | AccessShareLock       | t       | p_ci_builds_metadata
 relation      | ShareRowExclusiveLock | t       | p_ci_builds_metadata
 object        | AccessExclusiveLock   | t       |
 transactionid | ExclusiveLock         | t       |
 object        | AccessExclusiveLock   | t       |
 relation      | ShareRowExclusiveLock | t       | ci_builds_metadata
 relation      | ShareRowExclusiveLock | t       | ci_builds
 relation      | AccessExclusiveLock   | t       | ci_builds  -- <--
(10 rows)

gitlabhq_test_ci=# rollback;
ROLLBACK

Related to gitlab-com/gl-infra/production#8521 (closed)

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.