Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iQZG9-0004KK-4x for pgsql-bugs@arkaria.postgresql.org; Fri, 01 Nov 2019 15:54:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iQZG7-0002ij-Pm for pgsql-bugs@arkaria.postgresql.org; Fri, 01 Nov 2019 15:54:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from <9erthalion6@gmail.com>) id 1iQZG7-0002ic-FA for pgsql-bugs@lists.postgresql.org; Fri, 01 Nov 2019 15:54:43 +0000 Received: from mail-wr1-x441.google.com ([2a00:1450:4864:20::441]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <9erthalion6@gmail.com>) id 1iQZG4-0008I1-Ps for pgsql-bugs@lists.postgresql.org; Fri, 01 Nov 2019 15:54:42 +0000 Received: by mail-wr1-x441.google.com with SMTP id n1so10073696wra.10 for ; Fri, 01 Nov 2019 08:54:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=6lo2Bw3nKC8FMn3JjgBBuPnjBugD3NDSHovM0wA+RSE=; b=JoBAoZM62KnDaSxt0zG53w8N1cx6RJv+6NWDOxYC5pmIRYWYrap8iqbwL4uZtDWuHl FwHfduJbM6dcIRlO+fgtfJtDFOEo+Rgpt3c8OJxjiddfzn/nW0X8ntjeqbcW/O1GTlmf TkX0NAgKqBGy/AwjgRvn2/D+rLnPlwo4wI2/d0UZm/IOkunP4+WymbHcEEIiwOBSYUMs 5m/Pv6is50/9xj8SxuFCZypOEaPh7ZidREaK6z1dbb4HCZHAPFoabf0gzAQ+t1ErgZqK lGdWFKt8DYJSSBEHXtO9LiGymJMMqfqupogDqA2AeZ/bx5TZ8nhC8UMFnj7a5/0DdSt4 Wm1A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=6lo2Bw3nKC8FMn3JjgBBuPnjBugD3NDSHovM0wA+RSE=; b=AorcZZnrlXp9RsPeA9I6GiQO9UBFmvpCh3vU1Udd7s8dfdq1MnMVunwZXj9jIdBZ/7 9btFwt8ucua7/sxnUBH5JRgWwqfBYj7IvV2hTLD1HKuI9+IZeQ8CzppbmrurZ7Cfgokv xs1ZU3jhr8Zr7IBcOlQV5HUGJLEjRR2/IiwknVxTZgnu9QM58pO24zLrl/HEtDmmfa6v n8MhwfJxcfHYttn+QHEkS3xZMpqx67ABCoZFTcxNM1HcAnIIpoPkH+3hILgszu1zcqep IGPeIiXPcVD48GC77PZzdjqxje2t1tUGcY/OWNoBPutXOlAMX1fLxqLJyRGOy7gCkqm/ /BMw== X-Gm-Message-State: APjAAAUMF9xMkq4MDtx3ibhG3L9TKLL84GQtC8is3oYstWgjptQpgAoo 3ADolrqIB/HNBsfhGeyvMNI= X-Google-Smtp-Source: APXvYqw4dwj8gce51WdTvUXJqb/BP3ZZceluYRA5aXYwjMEIdL2t4+53A/Qxpf7sCW7+Iu2NVBMiGQ== X-Received: by 2002:a5d:4a03:: with SMTP id m3mr243567wrq.359.1572623679492; Fri, 01 Nov 2019 08:54:39 -0700 (PDT) Received: from localhost ([185.85.220.193]) by smtp.gmail.com with ESMTPSA id g14sm8478729wro.33.2019.11.01.08.54.38 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 01 Nov 2019 08:54:38 -0700 (PDT) Date: Fri, 1 Nov 2019 16:56:30 +0100 From: Dmitry Dolgov <9erthalion6@gmail.com> To: Alexey Ermakov Cc: Tom Lane , Sergei Kornilov , "pgsql-bugs@lists.postgresql.org" Subject: Re: BUG #16016: deadlock with startup process, AccessExclusiveLock on pg_statistic's toast table Message-ID: <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> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5DBBF7B5.6040002@dataegret.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk > 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.