Re: Support reset of Shared objects statistics in "pg_stat_reset" function

On Fri, 6 Aug 2021 at 17:40, Himanshu Upadhyaya
<upadhyaya(dot)himanshu(at)gmail(dot)com> wrote:
>
> Hi Sadhu,
>
>
> > The call to “pg_stat_reset“ does reset all the statistics data for
> > tables belonging to the current database but does not take care of
> > shared tables e.g pg_attribute.
>
> pg_attribute is not a shared catalog, is the mentioned scenario is also applicable for few others tables?

Yes, I think, by mistake, Sadhu has mentioned pg_attribute.

With patch, I checked for pg_database and verified that we are resetting stats.

psql (15devel)
Type "help" for help.

postgres=# SELECT * FROM pg_statio_all_tables where relid=1262;
relid | schemaname | relname | heap_blks_read | heap_blks_hit |
idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit |
tidx_blks_read | tidx_blks_hit
-------+------------+-------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
1262 | pg_catalog | pg_database | 1 | 2 |
2 | 0 | 0 | 0 |
0 | 0
(1 row)

postgres=#
postgres=# select pg_stat_reset();
pg_stat_reset
---------------

(1 row)

postgres=# SELECT * FROM pg_statio_all_tables where relid=1262;
relid | schemaname | relname | heap_blks_read | heap_blks_hit |
idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit |
tidx_blks_read | tidx_blks_hit
-------+------------+-------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
1262 | pg_catalog | pg_database | 0 | 0 |
0 | 0 | 0 | 0 |
0 | 0
(1 row)

postgres=#

Shared tables are:
1. pg_database -- DatabaseRelationId 1262
2. pg_tablespcae -- TableSpaceRelationId 1213
3. pg_authid -- AuthIdRelationId 1260
4. pg_auth_members -- AuthMemRelationId 1261
5. pg_shdescription -- SharedDescriptionRelationId 2396
6. pg_shdepend -- SharedDependRelationId 1214
7. pg_shseclabel -- SharedSecLabelRelationId 3592
8. pg_db_role_setting -- DbRoleSettingRelationId 2694
9. pg_replication_origin -- ReplicationOriginRelationId 6000
10. pg_subscription -- SubscriptionRelationId 6100

--
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com
>
> I have just tried it with-out your patch:
>
> postgres=# SELECT * FROM pg_statio_all_tables where relid=1249;
> relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
> -------+------------+--------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
> 1249 | pg_catalog | pg_attribute | 29 | 522 | 8 | 673 | | | |
> (1 row)
>
> postgres=# select pg_stat_reset();
> pg_stat_reset
> ---------------
>
> (1 row)
>
> postgres=# SELECT * FROM pg_statio_all_tables where relid=1249;
> relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
> -------+------------+--------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
> 1249 | pg_catalog | pg_attribute | 0 | 0 | 0 | 0 | | | |
>
>
> We are able to reset the stats of pg_attibute without your patch.
>
> Thanks,
> Himanshu
>
> On Fri, Aug 6, 2021 at 1:56 PM Sadhuprasad Patro <b(dot)sadhu(at)gmail(dot)com> wrote:
>>
>> Hi,
>>
>> The call to “pg_stat_reset“ does reset all the statistics data for
>> tables belonging to the current database but does not take care of
>> shared tables e.g pg_attribute. Similarly to reset the statistics at
>> table level, the call to “pg_stat_reset_single_table_counters“ does
>> not take care of shared tables.
>>
>> When we reset all the statistics using the call “pg_stat_reset”, the
>> postgres process internally makes calls to “
>> pgstat_recv_resetcounter“, which resets the statistics of all the
>> tables of the current database. But not resetting the statistics of
>> shared objects using database ID as 'InvalidOid'.
>>
>> The same fix is made in the internal function
>> “pgstat_recv_resetsinglecounter“ to reset the statistics for the
>> shared table for the call "pg_stat_reset_single_table_counters".
>>
>> --
>> thank u
>> SADHU PRASAD
>> EnterpriseDB: http://www.enterprisedb.com

--
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com