Re: Table locking during backup
От | Tomas Vondra |
---|---|
Тема | Re: Table locking during backup |
Дата | |
Msg-id | 20191007211930.r53ockkfjkx3gbzt@development обсуждение исходный текст |
Ответ на | Table locking during backup (Artur Zając <azajac@ang.com.pl>) |
Ответы |
RE: Table locking during backup
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: