What locks does PostgreSQL COPY method require?

Question

I would like to know the locks that the COPY method may require. When I run it, I see that it uses the lock SHARE ACCESS, I would like to know if it is the only one.

I also would like to know the difference between COPY and pg_dump. Do they use the same methods under the hood? Do they need the same locks? Can they use the same transaction isolation level?

Shiekh

Answer 1

EDIT:

The behavior described below was recognized as a bug and fixed in PostgreSQL v13. Now COPY TO will hold the lock until the end of the transaction.

---

The locks that COPY takes can be seen from the source of DoCopy in src/backend/commands/copy.c:

if (stmt->relation)
{
    LOCKMODE    lockmode = is_from ? RowExclusiveLock : AccessShareLock;
    [...]

    /* Open and lock the relation, using the appropriate lock type. */
    rel = table_openrv(stmt->relation, lockmode);

    [...]
}
[...]

/*
 * Close the relation. If reading, we can release the AccessShareLock we
 * got; if writing, we should hold the lock until end of transaction to
 * ensure that updates will be committed before lock is released.
 */
if (rel != NULL)
    table_close(rel, (is_from ? NoLock : AccessShareLock));

That means:

• COPY <table> FROM takes a ROW EXCLUSIVE lock, just like INSERT, UPDATE and DELETE.

• COPY <table> TO takes an ACCESS SHARE lock, just like SELECT

• Different from SELECT, COPY <table> TO does not hold the lock until the end of the transaction, but releases it immediately.

This is independent of the transaction isolation level.

pg_dump uses COPY to dump the table contents, so all the above applies to pg_dump as well. pg_dump always uses the REPEATABLE READ isolation level to get a consistent snapshot of the whole database.

Miniaturist

Are COPY TO and COPY FROM switched? I believe it's COPY table TO STDOUT that reads from the table, and COPY table FROM STDIN that writes to the table... – Nonconformity

You are right, I mixed up the lower part of the answer. Fixed. – Miniaturist