pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Drouvot, Bertrand <bdrouvot@amazon.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: pg_stat_have_stats() returns true for dropped indexes (or for index creation transaction rolled back)
Date: Mon, 22 Aug 2022 18:39:07 +0200
Message-ID: <51bbf286-2b4a-8998-bd12-eaae4b765d99@amazon.com> (raw )
Hi hackers,
While working on the relation stats split into table and index stats
[1], I noticed that currently pg_stat_have_stats() returns true for
dropped indexes (or for index creation transaction rolled back).
Example:
postgres=# create table bdt as select a from generate_series(1,1000) a;
SELECT 1000
postgres=# create index bdtidx on bdt(a);
CREATE INDEX
postgres=# select * from bdt where a = 30;
a
----
30
(1 row)
postgres=# SELECT 'bdtidx'::regclass::oid;
oid
-------
16395
(1 row)
postgres=# select pg_stat_have_stats('relation', 5, 16395);
pg_stat_have_stats
--------------------
t
(1 row)
postgres=# drop index bdtidx;
DROP INDEX
postgres=# select pg_stat_have_stats('relation', 5, 16395);
pg_stat_have_stats
--------------------
t
(1 row)
Please find attached a patch proposal to fix it.
It does contain additional calls to pgstat_create_relation() and
pgstat_drop_relation() as well as additional TAP tests.
[1]:
https://www.postgresql.org/message-id/5bfcf1a5-4224-9324-594b-725e704c95b1%40amazon.com
Regards,
--
Bertrand Drouvot
Amazon Web Services: https://aws.amazon.com
Attachments:
v1-0001-pgstat_drop_relation-for-indexes.patch (text/plain, 7.3K) download
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index d7192f35e3..de10d2ade8 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1248,6 +1248,8 @@ index_create(Relation heapRelation,
}
else
{
+ /* ensure that stats are dropped if transaction aborts */
+ pgstat_create_relation(indexRelation);
index_build(heapRelation, indexRelation, indexInfo, false, true);
}
@@ -2325,6 +2327,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
@@ -2349,6 +2354,7 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode)
CatalogTupleDelete(indexRelation, &tuple->t_self);
ReleaseSysCache(tuple);
+
table_close(indexRelation, RowExclusiveLock);
/*
diff --git a/src/test/recovery/t/030_stats_cleanup_replica.pl b/src/test/recovery/t/030_stats_cleanup_replica.pl
index cc92ddbb52..04fe8a0997 100644
--- a/src/test/recovery/t/030_stats_cleanup_replica.pl
+++ b/src/test/recovery/t/030_stats_cleanup_replica.pl
@@ -29,11 +29,12 @@ $node_standby->start;
my $sect = 'initial';
-my ($dboid, $tableoid, $funcoid) =
+my ($dboid, $tableoid, $indexoid, $funcoid) =
populate_standby_stats('postgres', 'public');
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 't');
+ $dboid, $tableoid, $indexoid, $funcoid, 't');
+drop_index_by_oid('postgres', $indexoid);
drop_table_by_oid('postgres', $tableoid);
drop_function_by_oid('postgres', $funcoid);
@@ -41,7 +42,7 @@ $sect = 'post drop';
my $primary_lsn = $node_primary->lsn('flush');
$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn);
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 'f');
+ $dboid, $tableoid, $indexoid, $funcoid, 'f');
## Test that stats are cleaned up on standby after dropping indirectly
@@ -52,11 +53,11 @@ $node_primary->safe_psql('postgres', "CREATE SCHEMA drop_schema_test1");
$primary_lsn = $node_primary->lsn('flush');
$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn);
-($dboid, $tableoid, $funcoid) =
+($dboid, $tableoid, $indexoid, $funcoid) =
populate_standby_stats('postgres', 'drop_schema_test1');
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 't');
+ $dboid, $tableoid, $indexoid, $funcoid, 't');
$node_primary->safe_psql('postgres', "DROP SCHEMA drop_schema_test1 CASCADE");
$sect = "post schema drop";
@@ -66,7 +67,7 @@ $node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn);
# verify table and function stats removed from standby
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 'f');
+ $dboid, $tableoid, $indexoid, $funcoid, 'f');
## Test that stats are cleaned up on standby after dropping database
@@ -77,10 +78,10 @@ $node_primary->safe_psql('postgres', "CREATE DATABASE test");
$primary_lsn = $node_primary->lsn('flush');
$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn);
-($dboid, $tableoid, $funcoid) = populate_standby_stats('test', 'public');
+($dboid, $tableoid, $indexoid, $funcoid) = populate_standby_stats('test', 'public');
# verify stats are present
-test_standby_func_tab_stats_status('test', $dboid, $tableoid, $funcoid, 't');
+test_standby_func_tab_stats_status('test', $dboid, $tableoid, $indexoid, $funcoid, 't');
test_standby_db_stats_status('test', $dboid, 't');
$node_primary->safe_psql('postgres', "DROP DATABASE test");
@@ -92,7 +93,7 @@ $node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn);
# Note that this connects to 'postgres' but provides the dboid of dropped db
# 'test' which we acquired previously
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 'f');
+ $dboid, $tableoid, $indexoid, $funcoid, 'f');
test_standby_db_stats_status('postgres', $dboid, 'f');
@@ -102,16 +103,16 @@ test_standby_db_stats_status('postgres', $dboid, 'f');
# NB: Can't test database stats, they're immediately repopulated when
# reconnecting...
$sect = "pre restart";
-($dboid, $tableoid, $funcoid) = populate_standby_stats('postgres', 'public');
+($dboid, $tableoid, $indexoid, $funcoid) = populate_standby_stats('postgres', 'public');
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 't');
+ $dboid, $tableoid, $indexoid, $funcoid, 't');
$node_standby->restart();
$sect = "post non-immediate";
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 't');
+ $dboid, $tableoid, $indexoid, $funcoid, 't');
# but gone after an immediate restart
$node_standby->stop('immediate');
@@ -120,7 +121,7 @@ $node_standby->start();
$sect = "post immediate restart";
test_standby_func_tab_stats_status('postgres',
- $dboid, $tableoid, $funcoid, 'f');
+ $dboid, $tableoid, $indexoid, $funcoid, 'f');
done_testing();
@@ -134,6 +135,9 @@ sub populate_standby_stats
$node_primary->safe_psql($connect_db,
"CREATE TABLE $schema.drop_tab_test1 AS SELECT generate_series(1,100) AS a"
);
+ $node_primary->safe_psql($connect_db,
+ "CREATE INDEX drop_tab_test1_idx on $schema.drop_tab_test1(a);"
+ );
$node_primary->safe_psql($connect_db,
"CREATE FUNCTION $schema.drop_func_test1() RETURNS VOID AS 'select 2;' LANGUAGE SQL IMMUTABLE"
);
@@ -145,6 +149,8 @@ sub populate_standby_stats
"SELECT oid FROM pg_database WHERE datname = '$connect_db'");
my $tableoid = $node_standby->safe_psql($connect_db,
"SELECT '$schema.drop_tab_test1'::regclass::oid");
+ my $indexoid = $node_standby->safe_psql($connect_db,
+ "SELECT '$schema.drop_tab_test1_idx'::regclass::oid");
my $funcoid = $node_standby->safe_psql($connect_db,
"SELECT '$schema.drop_func_test1()'::regprocedure::oid");
@@ -153,7 +159,7 @@ sub populate_standby_stats
"SELECT * FROM $schema.drop_tab_test1");
$node_standby->safe_psql($connect_db, "SELECT $schema.drop_func_test1()");
- return ($dboid, $tableoid, $funcoid);
+ return ($dboid, $tableoid, $indexoid, $funcoid);
}
sub drop_function_by_oid
@@ -176,14 +182,26 @@ sub drop_table_by_oid
$node_primary->safe_psql($connect_db, "DROP TABLE $table_name");
}
+sub drop_index_by_oid
+{
+ my ($connect_db, $indexoid) = @_;
+
+ # Get index name from returned oid
+ my $index_name =
+ $node_primary->safe_psql($connect_db, "SELECT '$indexoid'::regclass");
+ $node_primary->safe_psql($connect_db, "DROP index $index_name");
+}
+
sub test_standby_func_tab_stats_status
{
local $Test::Builder::Level = $Test::Builder::Level + 1;
- my ($connect_db, $dboid, $tableoid, $funcoid, $present) = @_;
+ my ($connect_db, $dboid, $tableoid, $indexoid, $funcoid, $present) = @_;
- my %expected = (rel => $present, func => $present);
+ my %expected = (idx=> $present, rel => $present, func => $present);
my %stats;
+ $stats{idx} = $node_standby->safe_psql($connect_db,
+ "SELECT pg_stat_have_stats('relation', $dboid, $indexoid)");
$stats{rel} = $node_standby->safe_psql($connect_db,
"SELECT pg_stat_have_stats('relation', $dboid, $tableoid)");
$stats{func} = $node_standby->safe_psql($connect_db,
view thread (12+ messages) latest in thread
Message-ID: <51bbf286-2b4a-8998-bd12-eaae4b765d99@amazon.com>
Permalink: ../51bbf286-2b4a-8998-bd12-eaae4b765d99@amazon.com/
Also on: postgresql.org/message-id/51bbf286-2b4a-8998-bd12-eaae4b765d99@amazon.com
copy link · copy postgr.es · copy git am
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-hackers@postgresql.org
Cc: bdrouvot@amazon.com
Subject: Re: pg_stat_have_stats() returns true for dropped indexes (or for index creation transaction rolled back)
In-Reply-To: <51bbf286-2b4a-8998-bd12-eaae4b765d99@amazon.com>
References: <51bbf286-2b4a-8998-bd12-eaae4b765d99@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