Re: Table locking during backup

On Mon, Oct 07, 2019 at 11:00:08PM +0200, Artur Zając wrote:
>Hi,
>
>
>
>I cannot reproduce some scenario I found in my PostgreSQL logs.
>
>
>
>I have two connections/processes:
>
>
>
>Process 24755 is standard pg_backup connection with:
>
>
>
>.
>
>BEGIN;
>SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY
>
>.
>
>LOCK TABLE gm.tableabc;
>
>.
>
>COPY FROM gm.tableabc
>
>
>
>Process 5969 is normal connection where I have:
>
>
>
>LOG: proces 5969 still waiting for AccessExclusiveLock on relations
>562888531 of database 16429 after 1000.066 ms
>
>DETAILT: Process holding the lock: 24755. Wait queue: 5969
>
>
>
>Query that is waiting (from proces 5969) is:
>
>
>
>CREATE TEMP TABLE IF NOT EXISTS tableabc
>
>(
>
>Id BIGINT DEFAULT gm.top()
>
>) INHERITS (gm.tableabc);
>
>
>
>I cannot reproduce what pg_dump is doing that causes waiting on proces 5969
>(which lock is acquired and why). When pg_dump ends lock is released and
>proces 5969 continues.
>
>
>
>I know that doing in two parallel connections:
>
>
>
>BEGIN;
>
>
>
>CREATE TEMP TABLE IF NOT EXISTS tableabc
>
>(
>
>Id BIGINT DEFAULT gm.top()
>
>) INHERITS (gm.tableabc);
>
>
>
>causes waiting state on second connection until the first finished, but
>pg_dump connection is read only.
>

Not sure why would it matter that the pg_dump connection is read-only,
this is about locking because pg_dump needs to ensure the schema does
not change while it's running.

pg_dump does not do

LOCK TABLE gm.tableabc;

but

LOCK TABLE gm.tableabc IN ACCESS SHARE MODE;

Which should be visible in pg_locks system view. And it does conflict
with the ACCESS EXCLUSIVE mode, used by the second query.

>
>
>Could you suggest me which part of pg_dump (which query) might cause that
>behaviour.
>

It's this line:

https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/pg_dump.c#L6676

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services