PostgreSQL Lock Conflicts

COPY TO

The COPY TO command acquires AccessShareLock table lock . Following are the locks COPY TO acquires, the commands that are allowed to run concurrently with its lock AccessShareLock and the commands that conflict with it. The list also includes the conflicting row locks if applicable

Locks acquired by COPY TO

1. AccessShareLock (table)

Commands concurrently allowed on the table with COPY TO

e.g. If tx1 does a COPY TO 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. VACUUM
14. REINDEX CONCURRENTLY
15. REFRESH MATERIALIZED VIEW CONCURRENTLY
16. DROP INDEX CONCURRENTLY
17. CREATE TRIGGER
18. CREATE STATISTICS
19. CREATE INDEX
20. CREATE INDEX CONCURRENTLY
21. COMMENT ON
22. ANALYZE
23. ALTER TABLE VALIDATE CONSTRAINT
24. ALTER TABLE SET WITHOUT CLUSTER
25. ALTER TABLE SET TOAST
26. ALTER TABLE SET STATISTICS
27. ALTER TABLE SET N_DISTINCT
28. ALTER TABLE SET FILLFACTOR
29. ALTER TABLE SET AUTOVACUUUM
30. ALTER TABLE ENABLE/DISABLE TRIGGER
31. ALTER TABLE DETACH PARTITION CONCURRENTLY (PARENT)
32. ALTER TABLE CLUSTER ON
33. ALTER TABLE ATTACH PARTITION (PARENT)
34. ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT)
35. ALTER TABLE ADD FOREIGN KEY NOT VALID (CHILD)
36. ALTER TABLE ADD FOREIGN KEY (PARENT)
37. ALTER TABLE ADD FOREIGN KEY (CHILD)
38. ALTER INDEX (RENAME)

Commands conflicting with COPY TO on the table

e.g. If tx1 does a COPY TO 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 COPY TO, tx2 will block.

1. VACUUM FULL
2. TRUNCATE
3. REINDEX
4. REFRESH MATERIALIZED VIEW
5. DROP TABLE
6. DROP INDEX
7. CLUSTER
8. ALTER TABLE SET/DROP DEFAULT
9. ALTER TABLE SET TABLESPACE
10. ALTER TABLE SET STORAGE
11. ALTER TABLE SET SEQUENCE
12. ALTER TABLE SET DATA TYPE
13. ALTER TABLE SET COMPRESSION
14. ALTER TABLE RESET STORAGE
15. ALTER TABLE RENAME
16. ALTER TABLE INHERIT PARENT
17. ALTER TABLE ENABLE/DISABLE RULE
18. ALTER TABLE ENABLE/DISABLE ROW LEVEL SECURITY
19. ALTER TABLE DROP EXPRESSION
20. ALTER TABLE DROP CONSTRAINT
21. ALTER TABLE DROP COLUMN
22. ALTER TABLE DETACH PARTITION (PARENT)
23. ALTER TABLE DETACH PARTITION (TARGET/DEFAULT)
24. ALTER TABLE DETACH PARTITION CONCURRENTLY (TARGET/DEFAULT)
25. ALTER TABLE ATTACH PARTITION (TARGET/DEFAULT)
26. ALTER TABLE ALTER CONSTRAINT
27. ALTER TABLE ADD COLUMN
28. ALTER TABLE ADD CONSTRAINT
29. ALTER INDEX SET TABLESPACE
30. ALTER INDEX SET FILLFACTOR
31. ALTER INDEX ATTACH PARTITION