Thread: 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

> 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
secondquery. 

My mistake. I cited the log incorrectly - of course should be (and there was) LOCK TABLE gm.tableabc IN ACCESS SHARE
MODE. 
After all, your answer does not explain everything. I tried to reproduce the minimum commands:

First session:

BEGIN;
set transaction isolation level repeatable read, read only;
lock TABLE gm.tableabc IN access share mode;


Second session:
BEGIN;
CREATE TEMP TABLE IF NOT EXISTS tableabc (Id BIGINT DEFAULT random()) INHERITS (gm.tableabc);

"CREATE TEMP TABLE" does not wait for anything. Waiting state is only when I start third session with the same queries
asin second. 

Maybe "LOCK TABLE ... IN ACCESS SHARE MODE" is the answer, but with correlation with some other query or setting setted
bypg_backup. 

Table gm.tableabc is created with "create table gm.tableabc (x int default random());"

AZ

=?utf-8?Q?Artur_Zaj=C4=85c?= <azajac@ang.com.pl> writes:
> First session:
> BEGIN;
> set transaction isolation level repeatable read, read only;
> lock TABLE gm.tableabc IN access share mode;

> Second session:
> BEGIN;
> CREATE TEMP TABLE IF NOT EXISTS tableabc (Id BIGINT DEFAULT random()) INHERITS (gm.tableabc);

> "CREATE TEMP TABLE" does not wait for anything. Waiting state is only when I start third session with the same
queriesas in second. 

A bit of looking into pg_locks will show you that CREATE TABLE
... INHERITS takes ShareUpdateExclusiveLock on the table being
inherited from.  Per the manual,

    SHARE UPDATE EXCLUSIVE

        Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW
        EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode
        protects a table against concurrent schema changes and VACUUM
        runs.

So that doesn't conflict with pg_dump, but would conflict with another
session trying to INHERIT from the same table.  AFAICS, pg_dump's lock
isn't involved in that conflict at all.

If you only notice blocking when pg_dump is running, it's likely not
due to this lock in isolation, but the fact that pg_dump takes
access-share locks on everything in sight.  That can block sessions
that are trying to do DDL, causing other things to queue up behind
them, depending on what other locks those session(s) already hold.

There's really no substitute for looking into pg_locks to see
what's going on in cases like this ...

            regards, tom lane