pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Drouvot, Bertrand <bdrouvot@amazon.com>
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: andres@anarazel.de
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, 25 Aug 2022 11:44:34 +0200
Message-ID: <be4c74ba-e229-126b-6dff-0c879c70c547@amazon.com> (raw)
In-Reply-To: <20220825.114727.330127256246111733.horikyota.ntt@gmail.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>

Hi,

On 8/25/22 4:47 AM, Kyotaro Horiguchi wrote:
> Good catch, and thanks for the patch!
>
> (The file name would correctly be v2-0001-...:)
>
> At Tue, 23 Aug 2022 09:58:03 +0200, "Drouvot, Bertrand" <bdrouvot@amazon.com> wrote in
>> Agree it's better to move it to heap_create(): it's done in the new
>> version attached.
> +1 (not considering stats splitting)
>
>> We'll see later on if it needs to be duplicated for the table/index
>> split work.
> The code changes looks good to me.

Thanks for looking at it!

> +-- pg_stat_have_stats returns true for table creation inserting data
> +-- pg_stat_have_stats returns true for committed index creation
> +
>
> Not sure we need this, as we check that already in the same file. (In
> other words, if we failed this, the should have failed earlier.)

That's right.

> Maybe
> we only need check for drop operations

Looking closer at it, I think we are already good for the drop case on 
the tables (by making direct use of the pg_stat_get_* functions on the 
before dropped oid).

So I think we can remove all the "table" new checks: new patch attached 
is doing so.

On the other hand, for the index case, I think it's better to keep the 
"committed index creation one".

Indeed, to check that the drop behaves correctly I think it's better in 
"the same test" to ensure we've had the desired result before the drop 
(I mean having pg_stat_have_stats() returning false after a drop does 
not really help if we are not 100% sure it was returning true for the 
exact same index before the drop).

> We have other variable-numbered stats kinds
> FUNCTION/REPLSLOT/SUBSCRIPTION. Don't we need the same for these?

I don't think we need more tests for the FUNCTION case (as it looks to 
me it is already covered in stat.sql by the pg_stat_get_function_calls() 
calls on the dropped functions oids).

For SUBSCRIPTION, i think this is covered in 026_stats.pl:

# Subscription stats for sub1 should be gone
is( $node_subscriber->safe_psql(
         $db, qq(SELECT pg_stat_have_stats('subscription', 0, $sub1_oid))),
     qq(f),
     qq(Subscription stats for subscription '$sub1_name' should be 
removed.));

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)

Attaching v3-0001 (with the right "numbering" this time ;-) )

Regards,

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

Attachments:

v3-0001-pgstat_drop_relation-for-indexes.patch (text/plain, 8.8K) download diff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.out index 78d36429c8..4526c7e87f 100644 --- a/contrib/test_decoding/expected/stats.out +++ b/contrib/test_decoding/expected/stats.out @@ -139,6 +139,14 @@ SELECT slot_name FROM pg_stat_replication_slots; COMMIT; DROP TABLE stats_test; +-- 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); + pg_stat_have_stats +-------------------- + t +(1 row) + SELECT pg_drop_replication_slot('regression_slot_stats1'), pg_drop_replication_slot('regression_slot_stats2'), pg_drop_replication_slot('regression_slot_stats3'); @@ -147,3 +155,10 @@ SELECT pg_drop_replication_slot('regression_slot_stats1'), | | (1 row) +-- pg_stat_have_stats returns false for regression_slot_stats1 +select pg_stat_have_stats('replslot', 0, 1); + pg_stat_have_stats +-------------------- + f +(1 row) + diff --git a/contrib/test_decoding/sql/stats.sql b/contrib/test_decoding/sql/stats.sql index 630371f147..036039b966 100644 --- a/contrib/test_decoding/sql/stats.sql +++ b/contrib/test_decoding/sql/stats.sql @@ -51,6 +51,12 @@ SELECT slot_name FROM pg_stat_replication_slots; COMMIT; DROP TABLE stats_test; + +-- 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); SELECT pg_drop_replication_slot('regression_slot_stats1'), pg_drop_replication_slot('regression_slot_stats2'), pg_drop_replication_slot('regression_slot_stats3'); +-- pg_stat_have_stats returns false for regression_slot_stats1 +select pg_stat_have_stats('replslot', 0, 1); diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 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.c index 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.out index 6b233ff4c0..94758224dc 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -783,6 +783,110 @@ SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname 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 oid AS dboid from pg_database where datname = current_database() \gset +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.sql index 096f00ce8b..1c340ffc03 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -390,6 +390,50 @@ SELECT pg_stat_have_stats('zaphod', 0, 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); +-- 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 oid AS dboid from pg_database where datname = current_database() \gset +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);

view thread (12+ messages)  latest in thread

Message-ID: <be4c74ba-e229-126b-6dff-0c879c70c547@amazon.com>
Permalink:  ../be4c74ba-e229-126b-6dff-0c879c70c547@amazon.com/
Also on:    postgresql.org/message-id/be4c74ba-e229-126b-6dff-0c879c70c547@amazon.com

 ·  · 

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, horikyota.ntt@gmail.com, andres@anarazel.de
  Subject: Re: pg_stat_have_stats() returns true for dropped indexes (or for index creation transaction rolled back)
  In-Reply-To: <be4c74ba-e229-126b-6dff-0c879c70c547@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>

* 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