PostgreSQL Lock Conflicts

Database engineering course | @hnasr | ☕

ALTER TABLE ENABLE/DISABLE TRIGGER

The ALTER TABLE ENABLE/DISABLE TRIGGER command acquires ShareRowExclusiveLock table lock . Following are the locks ALTER TABLE ENABLE/DISABLE TRIGGER acquires, the commands that are allowed to run concurrently with its lock ShareRowExclusiveLock and the commands that conflict with it. The list also includes the conflicting row locks if applicable

Locks acquired by ALTER TABLE ENABLE/DISABLE TRIGGER

  1. ShareRowExclusiveLock (table)

Commands concurrently allowed on the table with ALTER TABLE ENABLE/DISABLE TRIGGER

e.g. If tx1 does a ALTER TABLE ENABLE/DISABLE TRIGGER on the table then tx2 is allowed to do any of the following commands concurrently on the same table without being blocked. Some DMLs executed on the same rows may block, read more below.
  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. ALTER TABLE ADD FOREIGN KEY (PARENT)

      Commands conflicting with ALTER TABLE ENABLE/DISABLE TRIGGER on the table

      e.g. If tx1 does a ALTER TABLE ENABLE/DISABLE TRIGGER 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 ENABLE/DISABLE TRIGGER, tx2 will block.
      1. INSERT
      2. UPDATE (NO KEYS)
      3. UPDATE (KEYS)
      4. DELETE
      5. COPY FROM
      6. MERGE
      7. VACUUM
      8. VACUUM FULL
      9. TRUNCATE
      10. REINDEX
      11. REINDEX CONCURRENTLY
      12. REFRESH MATERIALIZED VIEW
      13. REFRESH MATERIALIZED VIEW CONCURRENTLY
      14. DROP TABLE
      15. DROP INDEX
      16. DROP INDEX CONCURRENTLY
      17. CREATE TRIGGER
      18. CREATE STATISTICS
      19. CREATE INDEX
      20. CREATE INDEX CONCURRENTLY
      21. COMMENT ON
      22. CLUSTER
      23. ANALYZE
      24. ALTER TABLE VALIDATE CONSTRAINT
      25. ALTER TABLE SET/DROP DEFAULT
      26. ALTER TABLE SET WITHOUT CLUSTER
      27. ALTER TABLE SET TOAST
      28. ALTER TABLE SET TABLESPACE
      29. ALTER TABLE SET STORAGE
      30. ALTER TABLE SET STATISTICS
      31. ALTER TABLE SET SEQUENCE
      32. ALTER TABLE SET N_DISTINCT
      33. ALTER TABLE SET FILLFACTOR
      34. ALTER TABLE SET DATA TYPE
      35. ALTER TABLE SET COMPRESSION
      36. ALTER TABLE SET AUTOVACUUUM
      37. ALTER TABLE RESET STORAGE
      38. ALTER TABLE RENAME
      39. ALTER TABLE INHERIT PARENT
      40. ALTER TABLE ENABLE/DISABLE TRIGGER
      41. ALTER TABLE ENABLE/DISABLE RULE
      42. ALTER TABLE ENABLE/DISABLE ROW LEVEL SECURITY
      43. ALTER TABLE DROP EXPRESSION
      44. ALTER TABLE DROP CONSTRAINT
      45. ALTER TABLE DROP COLUMN
      46. ALTER TABLE DETACH PARTITION (PARENT)
      47. ALTER TABLE DETACH PARTITION (TARGET/DEFAULT)
      48. ALTER TABLE DETACH PARTITION CONCURRENTLY (PARENT)
      49. ALTER TABLE DETACH PARTITION CONCURRENTLY (TARGET/DEFAULT)
      50. ALTER TABLE CLUSTER ON
      51. ALTER TABLE ATTACH PARTITION (PARENT)
      52. ALTER TABLE ATTACH PARTITION (TARGET/DEFAULT)
      53. ALTER TABLE ALTER CONSTRAINT
      54. ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT)
      55. ALTER TABLE ADD FOREIGN KEY NOT VALID (CHILD)
      56. ALTER TABLE ADD FOREIGN KEY (CHILD)
      57. ALTER TABLE ADD COLUMN
      58. ALTER TABLE ADD CONSTRAINT
      59. ALTER INDEX SET TABLESPACE
      60. ALTER INDEX SET FILLFACTOR
      61. ALTER INDEX ATTACH PARTITION
      62. ALTER INDEX (RENAME)