Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nHeb6-000100-77 for pgsql-bugs@arkaria.postgresql.org; Wed, 09 Feb 2022 04:28:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nHeb4-0005KF-3A for pgsql-bugs@arkaria.postgresql.org; Wed, 09 Feb 2022 04:28:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nHeb3-0005K4-Qx for pgsql-bugs@lists.postgresql.org; Wed, 09 Feb 2022 04:28:49 +0000 Received: from dormouse.elm.relay.mailchannels.net ([23.83.212.50]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nHeaz-00040H-SB for pgsql-bugs@lists.postgresql.org; Wed, 09 Feb 2022 04:28:48 +0000 X-Sender-Id: a2hosting|x-authuser|bench@silentmedia.com Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id 2BD29820F3F; Wed, 9 Feb 2022 04:28:44 +0000 (UTC) Received: from a2ss24.a2hosting.com (unknown [127.0.0.6]) (Authenticated sender: a2hosting) by relay.mailchannels.net (Postfix) with ESMTPA id 8F88E8218DA; Wed, 9 Feb 2022 04:28:43 +0000 (UTC) X-Sender-Id: a2hosting|x-authuser|bench@silentmedia.com Received: from a2ss24.a2hosting.com ([TEMPUNAVAIL]. [75.98.175.117]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384) by 100.103.158.127 (trex/6.4.3); Wed, 09 Feb 2022 04:28:44 +0000 X-MC-Relay: Neutral X-MailChannels-SenderId: a2hosting|x-authuser|bench@silentmedia.com X-MailChannels-Auth-Id: a2hosting X-Invention-Wipe: 662b1a4c09f1775f_1644380924067_667573654 X-MC-Loop-Signature: 1644380924067:978220339 X-MC-Ingress-Time: 1644380924067 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=silentmedia.com; s=default; h=Content-Transfer-Encoding:Content-Type: In-Reply-To:MIME-Version:Date:Message-ID:From:References:Cc:To:Subject:Sender :Reply-To:Content-ID:Content-Description:Resent-Date:Resent-From: Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id:List-Help: List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=DAHf1TmLiOlW+x9kuDv92l3+CSPJvifZ9pljDEd9aMg=; b=H26QJLSCWS+kVOJ84p+2TkCnMk pzgrxv6kJvIlUFJBcCPcxMmlT6C4aWHNX6i5aYhLXqSO52GnMXIuV9YgMxeEb3fE4sdb6Y+KoiP9y vwrHzTlf26DXFrr8gZTm7GfQ8U3KLKtZ8vLVocJO/1CswegKYxifcJZLuCDZwmQAi/oU8rouP0dOX f82wNtQbCw2W8tnHyW8lbPXtpgti/SttclobrDAIpljhZtzrmlMCbPVWiJza4ESfUPgMtha0gCuqb hSUBbxygl8txlL3FgLTRTgJy6mM2dbp62ECi7ikt9c5ZusBM03Cg2Y6tkSfIvJv5y0OxANWdyK6TS 6KSOrLNg==; Received: from 97-113-87-26.tukw.qwest.net ([97.113.87.26]:49713 helo=Rolo.local) by a2ss24.a2hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1nHeam-000iVz-7c; Tue, 08 Feb 2022 23:28:33 -0500 Subject: Re: BUG #17401: REINDEX TABLE CONCURRENTLY creates a race condition on a streaming replica To: Michael Paquier Cc: Peter Geoghegan , PostgreSQL mailing lists References: <17401-9df851bb16dde397@postgresql.org> From: Ben Chobot Message-ID: <07ad5e5b-2176-5c7d-dfa7-7f63231df22b@silentmedia.com> Date: Tue, 8 Feb 2022 20:28:39 -0800 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.16; rv:52.0) Gecko/20100101 PostboxApp/7.0.54 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US X-AuthUser: bench@silentmedia.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Michael Paquier wrote on 2/8/22 5:35 PM: > On Tue, Feb 08, 2022 at 01:23:34PM -0800, Peter Geoghegan wrote: >> I find the idea that we'd fail to WAL-log information that is needed >> during Hot Standby (to prevent this race condition) plausible. >> Michael? > Yeah, REINDEX relies on some existing index definition, so it feels > like we are missing a piece related to invalid indexes in all that. A > main difference is the lock level, as exclusive locks are getting > logged so the standby can react and wait on that. The 30-minute mark > is interesting. Ben, did you change any replication-related GUCs that > could influence that? Say, wal_receiver_timeout, hot_standby_feedback > or max_standby_streaming_delay? Oh, to be clear, the 30 minute mark is more "the loop has always failed this far into it" and sometimes that's 5 minutes and sometimes it's more, but I've never seen it take more than somewhere in the 20s. I was thinking it was just because of the race condition, but, to answer your question, yes, we have tuned some replication parameters. Here are the ones you asked about; did you want to see the value of any others? =# show wal_receiver_timeout ;  wal_receiver_timeout ──────────────────────  1min (1 row) 04:26:27 db: postgres@postgres, pid:29507 =# show hot_standby_feedback ;  hot_standby_feedback ──────────────────────  on (1 row) 04:26:40 db: postgres@postgres, pid:29507 =# show max_standby_streaming_delay ;  max_standby_streaming_delay ─────────────────────────────  10min (1 row)