Catalog views failed to show partitioned table information.
Hi,
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.
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?
Regards,
Suraj
Attachments:
pg_indexes_fix_for_partition_index.patchapplication/octet-stream; name=pg_indexes_fix_for_partition_index.patchDownload
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8630542..4e61f54 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -162,7 +162,7 @@ CREATE VIEW pg_indexes AS
JOIN pg_class I ON (I.oid = X.indexrelid)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
- WHERE C.relkind IN ('r', 'm') AND I.relkind = 'i';
+ WHERE C.relkind IN ('r', 'm','p') AND I.relkind IN ('i','I');
CREATE OR REPLACE VIEW pg_sequences AS
SELECT
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 3e61f50..12cd1c8 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -10,6 +10,13 @@ select relhassubclass from pg_class where relname = 'idxpart_idx';
f
(1 row)
+-- Check if that index is present in pg_indexes view:
+select * from pg_indexes where indexname ilike 'idxpart_idx%' order by 3;
+ schemaname | tablename | indexname | tablespace | indexdef
+------------+-----------+-------------+------------+-----------------------------------------------------------------
+ public | idxpart | idxpart_idx | | CREATE INDEX idxpart_idx ON ONLY public.idxpart USING btree (a)
+(1 row)
+
drop index idxpart_idx;
create table idxpart1 partition of idxpart for values from (0) to (10);
create table idxpart2 partition of idxpart for values from (10) to (100)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b68b8d2..e384cd2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1358,7 +1358,7 @@ pg_indexes| SELECT n.nspname AS schemaname,
JOIN pg_class i ON ((i.oid = x.indexrelid)))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
- WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char"));
+ WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"])));
pg_locks| SELECT l.locktype,
l.database,
l.relation,
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 400b7eb..5093256 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -6,6 +6,8 @@ create table idxpart (a int, b int, c text) partition by range (a);
-- It will be set after the first partition is created.
create index idxpart_idx on idxpart (a);
select relhassubclass from pg_class where relname = 'idxpart_idx';
+-- Check if that index is present in pg_indexes view:
+select * from pg_indexes where indexname ilike 'idxpart_idx%' order by 3;
drop index idxpart_idx;
create table idxpart1 partition of idxpart for values from (0) to (10);
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. Sothis
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.