From: Drouvot, Bertrand <bdrouvot@amazon.com>
To: Andres Freund <andres@anarazel.de>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: pg_stat_have_stats() returns true for dropped indexes (or for index creation transaction rolled back)
Date: Thu, 1 Sep 2022 08:40:54 +0200
Message-ID: <3cd209a4-e2a1-0ae6-91a3-c945eb7db248@amazon.com> (raw)
In-Reply-To: <20220831192657.jqhphpud2mxbzbom@awork3.anarazel.de>
References: <51bbf286-2b4a-8998-bd12-eaae4b765d99@amazon.com>
<20220822173654.y4h2smhc4kpkvoeg@awork3.anarazel.de>
<61a95a06-cf88-cd15-0412-172717943e18@amazon.com>
<20220825.114727.330127256246111733.horikyota.ntt@gmail.com>
<be4c74ba-e229-126b-6dff-0c879c70c547@amazon.com>
<20220831192657.jqhphpud2mxbzbom@awork3.anarazel.de>
Hi,
On 8/31/22 9:26 PM, Andres Freund wrote:
> Hiu,
>
> On 2022-08-25 11:44:34 +0200, Drouvot, Bertrand wrote:
>> For REPLSLOT, I agree that we can add one test: I added it in
>> contrib/test_decoding/sql/stats.sql. It relies on pg_stat_have_stats() (as
>> relying on pg_stat_replication_slots and/or pg_stat_get_replication_slot()
>> would not help that much for this test given that the slot has been removed
>> from ReplicationSlotCtl)
> As Horiguchi-san noted, we can't rely on specific indexes being used.
Yeah.
> I feel
> ok with the current coverage in that area, but if we *really* feel we need to
> test it, we'd need to count the number of indexes with slots before dropping
> the slot and after the drop.
Thanks for the suggestion, I'm coming up with this proposal in v4 attached:
* count the number of slots
* ensure we have at least one for which pg_stat_have_stats() returns true
* get the list of ids (true_ids) for which pg_stat_have_stats()
returns true
* drop all the slots
* get the list of ids (false_ids) for which pg_stat_have_stats()
returns false
* ensure that both lists (true_ids and false_ids) are the same
I don't "really" feel the need we need to test it but i think that this
thread was a good opportunity to try to test it.
That said, that's also fine for me if this test is not part of the patch.
Maybe a better/simpler option could be to expose a function to get the
slot id based on its name and then write a "simple" test with it? (If so
I think that would better to start another patch/thread).
>> +-- pg_stat_have_stats returns true for committed index creation
> Maybe another test for an uncommitted index creation would be good too?
You mean in addition to the "-- pg_stat_have_stats returns false for
rolled back index creation" one?
> Could you try running this test with debug_discard_caches = 1 - it's pretty
> easy to write tests in this area that aren't reliable timing wise.
Thanks for the suggestion. I did and it passed without any issues.
>> +CREATE table stats_test_tab1 as select generate_series(1,10) a;
>> +CREATE index stats_test_idx1 on stats_test_tab1(a);
>> +SELECT oid AS dboid from pg_database where datname = current_database() \gset
> Since you introduced this, maybe convert the other instance of this query at
> the end of the file as well?
yeah good point. In v4, I moved the dboid recording at the top and use
it when appropriate.
Regards,
--
Bertrand Drouvot
Amazon Web Services:https://aws.amazon.comAttachments:v4-0001-pgstat_drop_relation-for-indexes.patch (text/plain, 11.2K) downloaddiff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.outindex 78d36429c8..fa18fba7ba 100644--- a/contrib/test_decoding/expected/stats.out+++ b/contrib/test_decoding/expected/stats.out@@ -139,6 +139,19 @@ SELECT slot_name FROM pg_stat_replication_slots;
COMMIT;
DROP TABLE stats_test;
+-- Count the number of slots+select count(1) as nb_slots from pg_stat_replication_slots \gset+-- We want pg_stat_have_stats() to return true at least one time for the slots id range+select count(1) > 0 from generate_series(0, :nb_slots - 1) as n where pg_stat_have_stats('replslot', 0, n);+ ?column? +----------+ t+(1 row)++-- Record the replication_slots id(s) for which pg_stat_have_stats() returns true+select array(select n from generate_series(0, :nb_slots - 1) as n where pg_stat_have_stats('replslot', 0, n) order by n asc) as true_ids \gset+\set true_ids '''' :true_ids ''''+-- Drop the 3 slots
SELECT pg_drop_replication_slot('regression_slot_stats1'),
pg_drop_replication_slot('regression_slot_stats2'),
pg_drop_replication_slot('regression_slot_stats3');
@@ -147,3 +160,12 @@ SELECT pg_drop_replication_slot('regression_slot_stats1'),
| |
(1 row)
+-- We want the ones that were true to be false after dropping the slots+select array(select n from generate_series(0, :nb_slots - 1) as n where not pg_stat_have_stats('replslot', 0, n) order by n asc) as false_ids \gset+\set false_ids '''' :false_ids ''''+select :true_ids = :false_ids;+ ?column? +----------+ t+(1 row)+diff --git a/contrib/test_decoding/sql/stats.sql b/contrib/test_decoding/sql/stats.sqlindex 630371f147..ffb021f76d 100644--- a/contrib/test_decoding/sql/stats.sql+++ b/contrib/test_decoding/sql/stats.sql@@ -51,6 +51,23 @@ SELECT slot_name FROM pg_stat_replication_slots;
COMMIT;
DROP TABLE stats_test;
++-- Count the number of slots+select count(1) as nb_slots from pg_stat_replication_slots \gset++-- We want pg_stat_have_stats() to return true at least one time for the slots id range+select count(1) > 0 from generate_series(0, :nb_slots - 1) as n where pg_stat_have_stats('replslot', 0, n);++-- Record the replication_slots id(s) for which pg_stat_have_stats() returns true+select array(select n from generate_series(0, :nb_slots - 1) as n where pg_stat_have_stats('replslot', 0, n) order by n asc) as true_ids \gset+\set true_ids '''' :true_ids ''''++-- Drop the 3 slots
SELECT pg_drop_replication_slot('regression_slot_stats1'),
pg_drop_replication_slot('regression_slot_stats2'),
pg_drop_replication_slot('regression_slot_stats3');
++-- We want the ones that were true to be false after dropping the slots+select array(select n from generate_series(0, :nb_slots - 1) as n where not pg_stat_have_stats('replslot', 0, n) order by n asc) as false_ids \gset+\set false_ids '''' :false_ids ''''+select :true_ids = :false_ids;
\ No newline at end of file
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex 9b03579e6e..9a80ccdccd 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -403,6 +403,9 @@ heap_create(const char *relname,
recordDependencyOnTablespace(RelationRelationId, relid,
reltablespace);
+ /* ensure that stats are dropped if transaction aborts */+ pgstat_create_relation(rel);+
return rel;
}
@@ -1477,9 +1480,6 @@ heap_create_with_catalog(const char *relname,
if (oncommit != ONCOMMIT_NOOP)
register_on_commit_action(relid, oncommit);
- /* ensure that stats are dropped if transaction aborts */- pgstat_create_relation(new_rel_desc);-
/*
* ok, the relation has been cataloged, so close our relations and return
* the OID of the newly created relation.
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex d7192f35e3..61f1d3926a 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -2325,6 +2325,9 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode)
if (RELKIND_HAS_STORAGE(userIndexRelation->rd_rel->relkind))
RelationDropStorage(userIndexRelation);
+ /* ensure that stats are dropped if transaction commits */+ pgstat_drop_relation(userIndexRelation);+
/*
* Close and flush the index's relcache entry, to ensure relcache doesn't
* try to rebuild it while we're deleting catalog entries. We keep the
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.outindex 6b233ff4c0..fa0a2c6f7a 100644--- a/src/test/regress/expected/stats.out+++ b/src/test/regress/expected/stats.out@@ -18,6 +18,8 @@ SET enable_indexscan TO on;
SET enable_indexonlyscan TO off;
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
+-- record dboid for later use+SELECT oid AS dboid from pg_database where datname = current_database() \gset
-- save counters
BEGIN;
SET LOCAL stats_fetch_consistency = snapshot;
@@ -771,18 +773,121 @@ SELECT pg_stat_have_stats('bgwriter', 0, 0);
SELECT pg_stat_have_stats('zaphod', 0, 0);
ERROR: invalid statistics kind: "zaphod"
-- db stats have objoid 0
-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 1);+SELECT pg_stat_have_stats('database', :dboid, 1);
pg_stat_have_stats
--------------------
f
(1 row)
-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 0);+SELECT pg_stat_have_stats('database', :dboid, 0);
pg_stat_have_stats
--------------------
t
(1 row)
+-- pg_stat_have_stats returns true for committed index creation+CREATE table stats_test_tab1 as select generate_series(1,10) a;+CREATE index stats_test_idx1 on stats_test_tab1(a);+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+SET enable_seqscan TO off;+select a from stats_test_tab1 where a = 3;+ a +---+ 3+(1 row)++SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ t+(1 row)++-- pg_stat_have_stats returns false for dropped index with stats+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ t+(1 row)++DROP index stats_test_idx1;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ f+(1 row)++-- pg_stat_have_stats returns false for rolled back index creation+BEGIN;+CREATE index stats_test_idx1 on stats_test_tab1(a);+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+select a from stats_test_tab1 where a = 3;+ a +---+ 3+(1 row)++SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ t+(1 row)++ROLLBACK;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ f+(1 row)++-- pg_stat_have_stats returns true for reindex CONCURRENTLY+CREATE index stats_test_idx1 on stats_test_tab1(a);+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+select a from stats_test_tab1 where a = 3;+ a +---+ 3+(1 row)++SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ t+(1 row)++REINDEX index CONCURRENTLY stats_test_idx1;+-- false for previous oid+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ f+(1 row)++-- true for new oid+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ t+(1 row)++-- pg_stat_have_stats returns true for a rolled back drop index with stats+BEGIN;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ t+(1 row)++DROP index stats_test_idx1;+ROLLBACK;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ pg_stat_have_stats +--------------------+ t+(1 row)++-- put enable_seqscan back to on+SET enable_seqscan TO on;
-- ensure that stats accessors handle NULL input correctly
SELECT pg_stat_get_replication_slot(NULL);
pg_stat_get_replication_slot
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sqlindex 096f00ce8b..a63bde9e67 100644--- a/src/test/regress/sql/stats.sql+++ b/src/test/regress/sql/stats.sql@@ -16,6 +16,9 @@ SET enable_indexonlyscan TO off;
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
+-- record dboid for later use+SELECT oid AS dboid from pg_database where datname = current_database() \gset+
-- save counters
BEGIN;
SET LOCAL stats_fetch_consistency = snapshot;
@@ -387,9 +390,52 @@ SELECT pg_stat_have_stats('bgwriter', 0, 0);
-- unknown stats kinds error out
SELECT pg_stat_have_stats('zaphod', 0, 0);
-- db stats have objoid 0
-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 1);-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 0);+SELECT pg_stat_have_stats('database', :dboid, 1);+SELECT pg_stat_have_stats('database', :dboid, 0);++-- pg_stat_have_stats returns true for committed index creation+CREATE table stats_test_tab1 as select generate_series(1,10) a;+CREATE index stats_test_idx1 on stats_test_tab1(a);+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+SET enable_seqscan TO off;+select a from stats_test_tab1 where a = 3;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);++-- pg_stat_have_stats returns false for dropped index with stats+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+DROP index stats_test_idx1;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);++-- pg_stat_have_stats returns false for rolled back index creation+BEGIN;+CREATE index stats_test_idx1 on stats_test_tab1(a);+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+select a from stats_test_tab1 where a = 3;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+ROLLBACK;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);++-- pg_stat_have_stats returns true for reindex CONCURRENTLY+CREATE index stats_test_idx1 on stats_test_tab1(a);+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+select a from stats_test_tab1 where a = 3;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+REINDEX index CONCURRENTLY stats_test_idx1;+-- false for previous oid+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+-- true for new oid+SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);++-- pg_stat_have_stats returns true for a rolled back drop index with stats+BEGIN;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+DROP index stats_test_idx1;+ROLLBACK;+SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid);+-- put enable_seqscan back to on+SET enable_seqscan TO on;
-- ensure that stats accessors handle NULL input correctly
SELECT pg_stat_get_replication_slot(NULL);
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-hackers@postgresql.org
Cc: bdrouvot@amazon.com, andres@anarazel.de, horikyota.ntt@gmail.com
Subject: Re: pg_stat_have_stats() returns true for dropped indexes (or for index creation transaction rolled back)
In-Reply-To: <3cd209a4-e2a1-0ae6-91a3-c945eb7db248@amazon.com>
References: <51bbf286-2b4a-8998-bd12-eaae4b765d99@amazon.com> <20220822173654.y4h2smhc4kpkvoeg@awork3.anarazel.de> <61a95a06-cf88-cd15-0412-172717943e18@amazon.com> <20220825.114727.330127256246111733.horikyota.ntt@gmail.com> <be4c74ba-e229-126b-6dff-0c879c70c547@amazon.com> <20220831192657.jqhphpud2mxbzbom@awork3.anarazel.de> <3cd209a4-e2a1-0ae6-91a3-c945eb7db248@amazon.com>
* 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