Re: Support reset of Shared objects statistics in "pg_stat_reset" function - Mailing list pgsql-hackers

On Fri, 6 Aug 2021 at 17:40, Himanshu Upadhyaya
<upadhyaya.himanshu@gmail.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.sadhu@gmail.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