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:
/messages/by-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

Thank you for review and commit.

On Tue, Dec 18, 2018 at 1:12 PM Michael Paquier <michael@paquier.xyz> wrote:

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

--
--

Thanks & Regards,
Suraj kharage,
EnterpriseDB Corporation,
The Postgres Database Company.

*Are you updated: Latest version of EnterpriseDB Postgres Advanced Server
are 10.6.13, 9.6.11.18, 9.5.15.21, 9.4.19.28*

To reach Support Call:
US +1-732-331-1320 or 1-800-235-5891
UK +44-2033 7198 20 - BRAZIL+55-2129 5813 71 -
INDIA+91-20-66449612 Australia: +61 26145 2339.

Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb

PRIVACY & CONFIDENTIALITY NOTICE

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution,retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.