PostgreSQL Lock Conflicts

REFRESH MATERIALIZED VIEW CONCURRENTLY

The REFRESH MATERIALIZED VIEW CONCURRENTLY command acquires ExclusiveLock table lock . Following are the locks REFRESH MATERIALIZED VIEW CONCURRENTLY acquires, the commands that are allowed to run concurrently with its lock ExclusiveLock and the commands that conflict with it. The list also includes the conflicting row locks if applicable

Locks acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY

1. ExclusiveLock (table)

Commands concurrently allowed on the table with REFRESH MATERIALIZED VIEW CONCURRENTLY

e.g. If tx1 does a REFRESH MATERIALIZED VIEW CONCURRENTLY 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. COPY TO
3. ALTER TABLE ADD FOREIGN KEY (PARENT)

Commands conflicting with REFRESH MATERIALIZED VIEW CONCURRENTLY on the table

e.g. If tx1 does a REFRESH MATERIALIZED VIEW CONCURRENTLY 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 REFRESH MATERIALIZED VIEW CONCURRENTLY, tx2 will block.

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