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

Hi Sadhu,

Patch working as expected with shared tables, just one Minor comment on the
patch.
+ if (!dbentry)
+ return;
+
+ /*
+ * We simply throw away all the shared table entries by recreating
new
+ * hash table for them.
+ */
+ if (dbentry->tables != NULL)
+ hash_destroy(dbentry->tables);
+ if (dbentry->functions != NULL)
+ hash_destroy(dbentry->functions);
+
+ dbentry->tables = NULL;
+ dbentry->functions = NULL;
+
+ /*
+ * This creates empty hash tables for tables and functions.
+ */
+ reset_dbentry_counters(dbentry);

We already have the above code for non-shared tables, can we restrict
duplicate code?
one solution I can think of, if we can have a list with two elements and
iterate each element with
these common steps?

Thanks,
Himanshu

On Fri, Aug 6, 2021 at 5:40 PM 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?
>
> 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
>>
>