I happened to notice that COPY TO releases the ACCESS SHARE lock
on the table right when the command ends rather than holding it
until the end of the transaction:
From backend/commands/copy.c:
/*
* 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.
*/
heap_close(rel, (from ? NoLock : AccessShareLock));
This violates the two-phase locking protocol and means that,
for example, the second COPY from the same table in a
REPEATABLE READ transaction might fail or return nothing if
a concurrent transaction dropped or truncated the table in the
mean time.
Is this a bug or intentional (for example, to make pg_dump release
its locks early)? In the latter case, it warrants documentation.
I dug into the history:
The comment is from commit 4dded12faad, before which COPY TO also
released the lock immediately.
The early lock release was added in commit bd272cace63, but that
only reflected how the indexes were locked before.
So this behavior seems to go back all the way.
Yours,
Laurenz Albe