Postgres locks within a transaction
<p>I'm having trouble understanding how locks interact with transactions in Postgres.</p>

<p>When I run this (long) query, I am surprised by the high degree of locking that occurs:</p>

<pre><code>BEGIN;
TRUNCATE foo;
\COPY foo FROM 'backup.txt';
COMMIT;
</code></pre>

<p>The <a href="https://www.postgresql.org/docs/9.2/static/sql-copy.html" rel="noreferrer">documentation</a> for <code>\COPY</code> doesn't mention what level of lock it requires, but <a href="https://www.postgresql.org/message-id/10611.1014867684%40sss.pgh.pa.us" rel="noreferrer">this post</a> indicates that it only gets a RowExclusiveLock. But when I run this query during the <code>\COPY</code>:</p>

<pre><code>SELECT mode, granted FROM pg_locks
WHERE relation='foo'::regclass::oid;
</code></pre>

<p>I get this:</p>

<pre><code>mode    granted
RowExclusiveLock    true
ShareLock   true
AccessExclusiveLock true
</code></pre>

<p>Where the heck is that AccessExclusiveLock coming from? I assume it's coming from the <code>TRUNCATE</code>, which <a href="https://www.postgresql.org/docs/9.1/static/sql-truncate.html" rel="noreferrer">requires an AccessExclusiveLock</a>. But the <code>TRUNCATE</code> finishes quickly, so I'd expect the lock to release quickly as well. This leaves me with a few questions.</p>

<p>When a lock is acquired by a command within a transaction, is that lock released at the end of the command (before the end of the transaction)? If so, why do I observe the above behavior? If not, why not? In fact, since <a href="https://stackoverflow.com/questions/35188911/how-to-delete-many-rows-from-frequently-accessed-table#comment-59698242">transactions don't touch the table until the <code>COMMIT</code></a>, why does a <code>TRUNCATE</code> in a transaction need to block the table at all?</p>

<p>I don't see any discussion of this in the <a href="https://www.postgresql.org/docs/9.6/static/sql-begin.html" rel="noreferrer">documentation</a> for <a href="https://www.postgresql.org/docs/current/static/transaction-iso.html" rel="noreferrer">transactions</a> in PG.</p>