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
