PostgreSQL Lock Conflicts

Database engineering course | @hnasr | ☕

ALTER TABLE ADD CONSTRAINT

The ALTER TABLE ADD CONSTRAINT command acquires AccessExclusiveLock table lock . Following are the locks ALTER TABLE ADD CONSTRAINT acquires, the commands that are allowed to run concurrently with its lock AccessExclusiveLock and the commands that conflict with it. The list also includes the conflicting row locks if applicable

Locks acquired by ALTER TABLE ADD CONSTRAINT

  1. AccessExclusiveLock (table)

Commands concurrently allowed on the table with ALTER TABLE ADD CONSTRAINT

Nothing is allowed to run concurrently with ALTER TABLE ADD CONSTRAINT

        Commands conflicting with ALTER TABLE ADD CONSTRAINT on the table

        e.g. If tx1 does a ALTER TABLE ADD CONSTRAINT on the table then concurrently tx2 tries to do any of the following commands on the same table, tx2 will be blocked. Conversely, if tx1 executes any of the following commands and then tx2 concurrently tries to execute ALTER TABLE ADD CONSTRAINT, tx2 will block.
        1. SELECT
        2. SELECT FOR UPDATE
        3. SELECT FOR SHARE
        4. SELECT FOR NO KEY UPDATE
        5. SELECT FOR KEY SHARE
        6. COPY TO
        7. INSERT
        8. UPDATE (NO KEYS)
        9. UPDATE (KEYS)
        10. DELETE
        11. COPY FROM
        12. MERGE
        13. VACUUM
        14. VACUUM FULL
        15. TRUNCATE
        16. REINDEX
        17. REINDEX CONCURRENTLY
        18. REFRESH MATERIALIZED VIEW
        19. REFRESH MATERIALIZED VIEW CONCURRENTLY
        20. DROP TABLE
        21. DROP INDEX
        22. DROP INDEX CONCURRENTLY
        23. CREATE TRIGGER
        24. CREATE STATISTICS
        25. CREATE INDEX
        26. CREATE INDEX CONCURRENTLY
        27. COMMENT ON
        28. CLUSTER
        29. ANALYZE
        30. ALTER TABLE VALIDATE CONSTRAINT
        31. ALTER TABLE SET/DROP DEFAULT
        32. ALTER TABLE SET WITHOUT CLUSTER
        33. ALTER TABLE SET TOAST
        34. ALTER TABLE SET TABLESPACE
        35. ALTER TABLE SET STORAGE
        36. ALTER TABLE SET STATISTICS
        37. ALTER TABLE SET SEQUENCE
        38. ALTER TABLE SET N_DISTINCT
        39. ALTER TABLE SET FILLFACTOR
        40. ALTER TABLE SET DATA TYPE
        41. ALTER TABLE SET COMPRESSION
        42. ALTER TABLE SET AUTOVACUUUM
        43. ALTER TABLE RESET STORAGE
        44. ALTER TABLE RENAME
        45. ALTER TABLE INHERIT PARENT
        46. ALTER TABLE ENABLE/DISABLE TRIGGER
        47. ALTER TABLE ENABLE/DISABLE RULE
        48. ALTER TABLE ENABLE/DISABLE ROW LEVEL SECURITY
        49. ALTER TABLE DROP EXPRESSION
        50. ALTER TABLE DROP CONSTRAINT
        51. ALTER TABLE DROP COLUMN
        52. ALTER TABLE DETACH PARTITION (PARENT)
        53. ALTER TABLE DETACH PARTITION (TARGET/DEFAULT)
        54. ALTER TABLE DETACH PARTITION CONCURRENTLY (PARENT)
        55. ALTER TABLE DETACH PARTITION CONCURRENTLY (TARGET/DEFAULT)
        56. ALTER TABLE CLUSTER ON
        57. ALTER TABLE ATTACH PARTITION (PARENT)
        58. ALTER TABLE ATTACH PARTITION (TARGET/DEFAULT)
        59. ALTER TABLE ALTER CONSTRAINT
        60. ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT)
        61. ALTER TABLE ADD FOREIGN KEY NOT VALID (CHILD)
        62. ALTER TABLE ADD FOREIGN KEY (PARENT)
        63. ALTER TABLE ADD FOREIGN KEY (CHILD)
        64. ALTER TABLE ADD COLUMN
        65. ALTER TABLE ADD CONSTRAINT
        66. ALTER INDEX SET TABLESPACE
        67. ALTER INDEX SET FILLFACTOR
        68. ALTER INDEX ATTACH PARTITION
        69. ALTER INDEX (RENAME)