Обсуждение: Catalog views failed to show partitioned table information.

On Fri, Dec 14, 2018 at 05:21:49PM +0530, Suraj Kharage wrote:
> There are some catalog views which do not show the partitioned table and
> its index entry.
> One of them is "pg_indexes" which failed to show the partitioned index.
> Attached the patch which fixes the same.

I tend to agree with your comment here.  pg_tables lists partitioned
tables, but pg_indexes is forgotting about partitioned indexes.  So this
is a good thing to add.

> Other views such as pg_stat*,pg_statio_* has the same problem for
> partitioned tables and indexes.
> Since the partitioned tables and its indexes considered as a dummy, they do
> not have any significance in stat tables,
> can we still consider adding relkind=p in these pg_stat_* views? Thoughts?

I am less sure about that as partitioned relations do not have a
physical presence.
--
Michael

Hi,

On 2018/12/15 8:00, Michael Paquier wrote:
> On Fri, Dec 14, 2018 at 05:21:49PM +0530, Suraj Kharage wrote:
>> There are some catalog views which do not show the partitioned table and
>> its index entry.
>> One of them is "pg_indexes" which failed to show the partitioned index.
>> Attached the patch which fixes the same.
> 
> I tend to agree with your comment here.  pg_tables lists partitioned
> tables, but pg_indexes is forgotting about partitioned indexes.  So this
> is a good thing to add.

+1

>> Other views such as pg_stat*,pg_statio_* has the same problem for
>> partitioned tables and indexes.
>> Since the partitioned tables and its indexes considered as a dummy, they do
>> not have any significance in stat tables,
>> can we still consider adding relkind=p in these pg_stat_* views? Thoughts?
> 
> I am less sure about that as partitioned relations do not have a
> physical presence.

Hmm, although most of the fields of pg_stat_user_tables would be NULL or 0
for partitioned tables/indexes, values of at least some of the fields of
pg_stat_user_tables, like last_vacuum, last_analyze, etc., might be useful
to users.  Also, we cannot assume that these views will continue to be
mostly useless as far as partitioned relations are concerned.

Thanks,
Amit

On Mon, Dec 17, 2018 at 10:22:28AM +0900, Amit Langote wrote:
> On 2018/12/15 8:00, Michael Paquier wrote:
>> I tend to agree with your comment here.  pg_tables lists partitioned
>> tables, but pg_indexes is forgotting about partitioned indexes.  So this
>> is a good thing to add.
>
> +1

I'll go commit something close to what Suraj is proposing if there are
no objections from others.  At least we agree on that part ;)

>> I am less sure about that as partitioned relations do not have a
>> physical presence.
>
> Hmm, although most of the fields of pg_stat_user_tables would be NULL or 0
> for partitioned tables/indexes, values of at least some of the fields of
> pg_stat_user_tables, like last_vacuum, last_analyze, etc., might be useful
> to users.  Also, we cannot assume that these views will continue to be
> mostly useless as far as partitioned relations are concerned.

Well, when VACUUM or ANALYZE list a partitioned table what the
processing does is to decompose partitioned tables into a list of actual
relations it can work on, and it never processes the partitioned parts,
so last_vacuum & friends remain set at 0/NULL.

We had a similar discussion about that a couple of months ago, and it
was not really clear to me how it is possible to define aggregates for
partitioned tables when analyzing them, and if stat tables should show
them or not:
https://www.postgresql.org/message-id/152922564661.24801.3078728743990100425@wrigleys.postgresql.org

Listing only NULL/0 is also confusing I think because this would mean
for the end-user that VACUUM and/or ANALYZE have never been run for a
given relation.

pg_partition_tree has been added since then, so compiling stats has
become easier for full partition trees, the documentation could be
improved on that point perhaps.
--
Michael

On Mon, Dec 17, 2018 at 11:01:59AM +0900, Michael Paquier wrote:
> On Mon, Dec 17, 2018 at 10:22:28AM +0900, Amit Langote wrote:
>> On 2018/12/15 8:00, Michael Paquier wrote:
>>> I tend to agree with your comment here.  pg_tables lists partitioned
>>> tables, but pg_indexes is forgotting about partitioned indexes.  So this
>>> is a good thing to add.
>>
>> +1
>
> I'll go commit something close to what Suraj is proposing if there are
> no objections from others.  At least we agree on that part ;)

And this part is done.
--
Michael