Re: Table locking during backup

=?utf-8?Q?Artur_Zaj=C4=85c?= <azajac(at)ang(dot)com(dot)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 queries as 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