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 1oTd9n-0003NP-2E for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Sep 2022 05:54:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oTd9l-0000Bn-TO for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Sep 2022 05:54:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oTd9k-0000Be-NJ for pgsql-hackers@lists.postgresql.org; Thu, 01 Sep 2022 05:54:25 +0000 Received: from smtp-fw-80006.amazon.com ([99.78.197.217]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oTd9h-0004KJ-UB for pgsql-hackers@lists.postgresql.org; Thu, 01 Sep 2022 05:54:24 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=amazon.com; i=@amazon.com; q=dns/txt; s=amazon201209; t=1662011662; x=1693547662; h=message-id:date:mime-version:subject:to:cc:references: from:in-reply-to; bh=dp2gl3iUjgYdFn3S3z/5JioHNsvdRZTk+GdvPRYcfm0=; b=LQzXAFpW7IemRZ19PgHi0gEz9Ygj7hAMrYv7oIvyMrr7WEeT/rhbV94Q HYJ4BWw15ZG0FpcPL6RLiJRzSyiyTRcXcK9I/9nh0IdhrTP8pF82DCRIK 0Pmdhfwd6wQPYhNI7ER9GuozhRiTA18zG0i6u5RgZi0CvBKem5/iCeJXj M=; X-IronPort-AV: E=Sophos;i="5.93,280,1654560000"; d="scan'208,217";a="125547672" Received: from pdx4-co-svc-p1-lb2-vlan2.amazon.com (HELO email-inbound-relay-pdx-2c-90419278.us-west-2.amazon.com) ([10.25.36.210]) by smtp-border-fw-80006.pdx80.corp.amazon.com with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 01 Sep 2022 05:54:05 +0000 Received: from EX13D03EUC003.ant.amazon.com (pdx1-ws-svc-p6-lb9-vlan2.pdx.amazon.com [10.236.137.194]) by email-inbound-relay-pdx-2c-90419278.us-west-2.amazon.com (Postfix) with ESMTPS id 7335A451E2; Thu, 1 Sep 2022 05:54:05 +0000 (UTC) Received: from [10.95.84.20] (10.43.161.228) by EX13D03EUC003.ant.amazon.com (10.43.164.192) with Microsoft SMTP Server (TLS) id 15.0.1497.38; Thu, 1 Sep 2022 05:54:02 +0000 Content-Type: multipart/alternative; boundary="------------qwdGgNAyobv4756TLkuFMuNz" Message-ID: Date: Thu, 1 Sep 2022 07:53:58 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.2.0 Subject: Re: pg_stat_have_stats() returns true for dropped indexes (or for index creation transaction rolled back) To: Kyotaro Horiguchi CC: , References: <61a95a06-cf88-cd15-0412-172717943e18@amazon.com> <20220825.114727.330127256246111733.horikyota.ntt@gmail.com> <20220831.161030.2252231560742777910.horikyota.ntt@gmail.com> Content-Language: en-US From: "Drouvot, Bertrand" In-Reply-To: <20220831.161030.2252231560742777910.horikyota.ntt@gmail.com> X-Originating-IP: [10.43.161.228] X-ClientProxiedBy: EX13D37UWC003.ant.amazon.com (10.43.162.183) To EX13D03EUC003.ant.amazon.com (10.43.164.192) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------qwdGgNAyobv4756TLkuFMuNz Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: 7bit Hi, On 8/31/22 9:10 AM, Kyotaro Horiguchi wrote: > Thanks for the searching. > +-- pg_stat_have_stats returns true for regression_slot_stats1 > +-- Its index is 1 in ReplicationSlotCtl->replication_slots > +select pg_stat_have_stats('replslot', 0, 1); > > This is wrong. The index is actually 0. Right, thanks for pointing out. (gdb) p get_replslot_index("regression_slot_stats1") $1 = 0 (gdb) p get_replslot_index("regression_slot_stats2") $2 = 1 (gdb) p get_replslot_index("regression_slot_stats3") $3 = 2 > We cannot know the id > reliably since we don't expose it at all. Right. > We could slightly increase > robustness by assuming the range of the id but that is just moving the > problem to another place. If the test is broken by a change of > replslot id assignment policy, it would be easily found and fixed. > > So is it fine simply fixing the comment with the correct ID? > > Or, contrarily we can be more sensitive to the change of ID assignment > policy by checking all the replication slots. > > select count(n) from generate_series(0, 2) as n where pg_stat_have_stats('replslot', 0, n); > > The number changes from 3 to 0 across the slots drop.. If any of the > slots has gone out of the range, the number before the drop decreases. Thanks for the ideas! I'm coming up with a slightly different one (also based on Andre's feedback in [1]) in the upcoming v4. [1]: https://www.postgresql.org/message-id/20220831192657.jqhphpud2mxbzbom%40awork3.anarazel.de Regards, -- Bertrand Drouvot Amazon Web Services:https://aws.amazon.com --------------qwdGgNAyobv4756TLkuFMuNz Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: 7bit

Hi,

On 8/31/22 9:10 AM, Kyotaro Horiguchi wrote:
Thanks for the searching.
+-- pg_stat_have_stats returns true for regression_slot_stats1
+-- Its index is 1 in ReplicationSlotCtl->replication_slots
+select pg_stat_have_stats('replslot', 0, 1);

This is wrong. The index is actually 0.  

Right, thanks for pointing out.

(gdb) p get_replslot_index("regression_slot_stats1")
$1 = 0
(gdb) p get_replslot_index("regression_slot_stats2")
$2 = 1
(gdb) p get_replslot_index("regression_slot_stats3")

$3 = 2

We cannot know the id
reliably since we don't expose it at all.

Right.

 We could slightly increase
robustness by assuming the range of the id but that is just moving the
problem to another place. If the test is broken by a change of
replslot id assignment policy, it would be easily found and fixed.

So is it fine simply fixing the comment with the correct ID?

Or, contrarily we can be more sensitive to the change of ID assignment
policy by checking all the replication slots.

select count(n) from generate_series(0, 2) as n where pg_stat_have_stats('replslot', 0, n);

The number changes from 3 to 0 across the slots drop.. If any of the
slots has gone out of the range, the number before the drop decreases.

Thanks for the ideas! I'm coming up with a slightly different one (also based on Andre's feedback in [1]) in the upcoming v4.

[1]: https://www.postgresql.org/message-id/20220831192657.jqhphpud2mxbzbom%40awork3.anarazel.de

Regards,

-- 
Bertrand Drouvot
Amazon Web Services: https://aws.amazon.com
--------------qwdGgNAyobv4756TLkuFMuNz--