PostgreSQL Lock Conflicts

ALTER TABLE SET N_DISTINCT

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

Locks acquired by ALTER TABLE SET N_DISTINCT

1. ShareUpdateExclusiveLock (table)

Commands concurrently allowed on the table with ALTER TABLE SET N_DISTINCT

e.g. If tx1 does a ALTER TABLE SET N_DISTINCT 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. INSERT
8. UPDATE (NO KEYS)
9. UPDATE (KEYS)
10. DELETE
11. COPY FROM
12. MERGE
13. ALTER TABLE ADD FOREIGN KEY (PARENT)

Commands conflicting with ALTER TABLE SET N_DISTINCT on the table

e.g. If tx1 does a ALTER TABLE SET N_DISTINCT 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 SET N_DISTINCT, tx2 will block.

1. VACUUM
2. VACUUM FULL
3. TRUNCATE
4. REINDEX
5. REINDEX CONCURRENTLY
6. REFRESH MATERIALIZED VIEW
7. REFRESH MATERIALIZED VIEW CONCURRENTLY
8. DROP TABLE
9. DROP INDEX
10. DROP INDEX CONCURRENTLY
11. CREATE TRIGGER
12. CREATE STATISTICS
13. CREATE INDEX
14. CREATE INDEX CONCURRENTLY
15. COMMENT ON
16. CLUSTER
17. ANALYZE
18. ALTER TABLE VALIDATE CONSTRAINT
19. ALTER TABLE SET/DROP DEFAULT
20. ALTER TABLE SET WITHOUT CLUSTER
21. ALTER TABLE SET TOAST
22. ALTER TABLE SET TABLESPACE
23. ALTER TABLE SET STORAGE
24. ALTER TABLE SET STATISTICS
25. ALTER TABLE SET SEQUENCE
26. ALTER TABLE SET N_DISTINCT
27. ALTER TABLE SET FILLFACTOR
28. ALTER TABLE SET DATA TYPE
29. ALTER TABLE SET COMPRESSION
30. ALTER TABLE SET AUTOVACUUUM
31. ALTER TABLE RESET STORAGE
32. ALTER TABLE RENAME
33. ALTER TABLE INHERIT PARENT
34. ALTER TABLE ENABLE/DISABLE TRIGGER
35. ALTER TABLE ENABLE/DISABLE RULE
36. ALTER TABLE ENABLE/DISABLE ROW LEVEL SECURITY
37. ALTER TABLE DROP EXPRESSION
38. ALTER TABLE DROP CONSTRAINT
39. ALTER TABLE DROP COLUMN
40. ALTER TABLE DETACH PARTITION (PARENT)
41. ALTER TABLE DETACH PARTITION (TARGET/DEFAULT)
42. ALTER TABLE DETACH PARTITION CONCURRENTLY (PARENT)
43. ALTER TABLE DETACH PARTITION CONCURRENTLY (TARGET/DEFAULT)
44. ALTER TABLE CLUSTER ON
45. ALTER TABLE ATTACH PARTITION (PARENT)
46. ALTER TABLE ATTACH PARTITION (TARGET/DEFAULT)
47. ALTER TABLE ALTER CONSTRAINT
48. ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT)
49. ALTER TABLE ADD FOREIGN KEY NOT VALID (CHILD)
50. ALTER TABLE ADD FOREIGN KEY (CHILD)
51. ALTER TABLE ADD COLUMN
52. ALTER TABLE ADD CONSTRAINT
53. ALTER INDEX SET TABLESPACE
54. ALTER INDEX SET FILLFACTOR
55. ALTER INDEX ATTACH PARTITION
56. ALTER INDEX (RENAME)