pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (7+ messages)  latest in thread

Message-ID: <20191101155630.2cbmabxo4kahri43@localhost>
Permalink:  ../20191101155630.2cbmabxo4kahri43@localhost/
Also on:    postgresql.org/message-id/20191101155630.2cbmabxo4kahri43@localhost

 · 

reply

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