What locks does PostgreSQL COPY method require?

user_13482518 0
Viewed 1974

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?

pg-dump psql locking postgresql
1 Answers

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.

user_64643080
Related