From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Alexey Ermakov <alexey.ermakov@dataegret.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Sergei Kornilov <sk@zsrv.org>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16016: deadlock with startup process, AccessExclusiveLock on pg_statistic's toast table
Date: Fri, 1 Nov 2019 16:56:30 +0100
Message-ID: <20191101155630.2cbmabxo4kahri43@localhost> (raw)
In-Reply-To: <5DBBF7B5.6040002@dataegret.com>
References: <16016-ed0345229ddc484c@postgresql.org>
<158201568991600@myt5-2a1eccb4c218.qloud-c.yandex.net>
<21601.1569179167@sss.pgh.pa.us>
<5DBBF7B5.6040002@dataegret.com>
> On Fri, Nov 01, 2019 at 03:15:33PM +0600, Alexey Ermakov wrote:
>
> I reproduced Sergei's test case on postgresql 11.5, replica hung up almost
> immediately after pgbench ran.
>
> 9907 - pid of startup process, 16387 - oid of test table, 2619 - oid of
> pg_statistic, 2840 - oid of toast table of pg_statistic.
>
> 1) pgbench on replica with 1 concurrent processes (-c 1):
>
> this case looks a bit different from what happened on initial report (which
> recently happened again btw) because at this time I can't even open new
> connection via psql or run query with pg_stat_activity - in hangs (pg_locks
> query works).
> perhaps because this time access exclusive lock is on pg_statistic table
> too, not only on it's toast table.
Interesting. I've tried the test case from previous email on the master
branch, and looks like I've got something similar with similar
stacktraces. After a short investigation, it looks pretty strange, a
backend 12682 is waiting on a lock, taken by 12584 (startup process):
[12682] LOG: process 12682 still waiting for AccessShareLock on relation 2619 of database 16384 after 1000.038 ms
[12682] DETAIL: Process holding the lock: 12584. Wait queue: 12689, 12674, 12671, 12682, 12683, 12677, 12680, 12676, 12686, 12670, 12678, 12688, 12679, 12681, 12684, 12685, 12687.
[12682] STATEMENT: select * from tablename where i = 95;
And if I understand correctly, startup process is waiting inside
ResolveRecoveryConflictWithVirtualXIDs with a waitlist, containing
backendId = 14:
>>> p *waitlist
$3 = {
backendId = 14,
localTransactionId = 218
}
>>> p allProcs[pgprocnos]
...
lxid = 218,
pid = 12682,
pgprocno = 87,
backendId = 14,
databaseId = 16384,
...
So the same backend 12682, although I'm not sure yet why.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: 9erthalion6@gmail.com, alexey.ermakov@dataegret.com, tgl@sss.pgh.pa.us, sk@zsrv.org
Subject: Re: BUG #16016: deadlock with startup process, AccessExclusiveLock on pg_statistic's toast table
In-Reply-To: <20191101155630.2cbmabxo4kahri43@localhost>
References: <16016-ed0345229ddc484c@postgresql.org> <158201568991600@myt5-2a1eccb4c218.qloud-c.yandex.net> <21601.1569179167@sss.pgh.pa.us> <5DBBF7B5.6040002@dataegret.com> <20191101155630.2cbmabxo4kahri43@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox