Re: statistics for shared catalogs not updated when autovacuum is off

On Tue, Feb 2, 2016 at 10:38 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> On 2/1/16 7:20 PM, Peter Eisentraut wrote:
>> That's probably right. Even with autovacuum on, the statistics for
>> shared catalogs do not appear as updated right away. That is, if you
>> run VACUUM and then look at pg_stat_sys_tables right away, you will see
>> the stats for shared catalogs to be slightly out of date until the
>> minutely autovacuum check causes them to update.
>>
>> So the problem exists in general, but the autovacuum launcher papers
>> over it every minute.
>
> I suspect the issue is in backend_read_statsfile(). Presumably the if just
> needs a call to AutoVacuumingActive() added:
>
> The interesting thing is that we always start the launcher one time, to
> protect against wraparound, but apparently that path doesn't call anything
> that calls backend_read_statsfile() (which is static).

The problem is different I think. Since 9.3, database-related
statistics are located on separate files. And the statistics of shared
tables is visibly located in a file with database name set as
InvalidOid, leading to the presence of db_0.stat in pg_stat_tmp. So
the fix for shared relations is to make sure that
backend_read_statsfile can load the file dedicated to shared objects
when data from it is needed, like pg_database stats. So making
backend_read_statsfile a bit smarter looks like the good answer to me.

At the same time I am not getting why pgstat_fetch_stat_tabentry needs
to be that complicated. Based on the relation OID we can know if it is
a shared relation or not, there is no point in doing two times the
same lookup in the pgstat hash table.

Attached is a patch that fixes the issue here:
=# show autovacuum;
autovacuum
------------
off
(1 row)
=# select seq_scan from pg_stat_sys_tables where relname = 'pg_database';
seq_scan
----------
2
(1 row)
=# select count(*) from pg_database;
count
-------
4
(1 row)
=# select seq_scan from pg_stat_sys_tables where relname = 'pg_database';
seq_scan
----------
3
(1 row)
--
Michael