Re: relkind='p' has no pg_stat_user_tables

On 05/03/2018 07:14 AM, Justin Pryzby wrote:
> I (finally) realized that my script for ANALYZEing parents of table hierarchies
> every month or so was looping around the same parent tables every night due to
> no stats for date of last last analysis.

Would help to see the script.

>
> I guess that's deliberate/known and maybe related to relkind='p' having no
> relfilenode.

Not sure that is an issue as pg_stat_user uses relid not relfilenode:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW

For reasons why they are often not the same:

https://www.postgresql.org/docs/10/static/storage-file-layout.html

"
Caution

Note that while a table's filenode often matches its OID, this is not
necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
and some forms of ALTER TABLE, can change the filenode while preserving
the OID. Avoid assuming that filenode and table OID are the same. Also,
for certain system catalogs including pg_class itself,
pg_class.relfilenode contains zero. The actual filenode number of these
catalogs is stored in a lower-level data structure, and can be obtained
using the pg_relation_filenode() function.
"

>
> Is there any good workaround other than making stampfiles or making my own
> "last analyzed" table?
>
> Thanks,
> Justin
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com