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.
Edited by Marius Bobin