Hi Kieth, On 2017/01/10 14:44, Keith Fiske wrote: > Is there any reason for the exclusion of parent tables from the pg_tables > system catalog view? They do not show up in information_schema.tables as > well. I believe I found where to make the changes and I tested to make sure > it works for my simple case. Attached is my first attempt at patching > anything in core. Not sure if there's anywhere else this would need to be > fixed.
Advertising
That's an oversight. The original partitioning patch didn't touch information_schema.sql and system_views.sql at all. I added the relkind = 'P' check in some other views as well, including what your patch considered. Thanks, Amit
>From 9eef3e87b9a025d233aa4b935b50bb0c7633efbb Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Tue, 10 Jan 2017 18:12:25 +0900 Subject: [PATCH] information_schema/system_views.sql and relkind 'P' Currently, partitioned table are not taken into account in various information_schema and system views. Reported by: Keith Fiske Patch by: Kieth Fiske, Amit Langote Reports: https://www.postgresql.org/message-id/CAG1_KcDJiZB=l6youo_bvufj2q2851_xdkfhw0jdcd_2vtk...@mail.gmail.com --- src/backend/catalog/information_schema.sql | 37 +++++++++++++++--------------- src/backend/catalog/system_views.sql | 3 ++- 2 files changed, 21 insertions(+), 19 deletions(-) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 4df390a763..318f195b81 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -453,7 +453,7 @@ CREATE VIEW check_constraints AS AND a.attnum > 0 AND NOT a.attisdropped AND a.attnotnull - AND r.relkind = 'r' + AND r.relkind IN ('r', 'P') AND pg_has_role(r.relowner, 'USAGE'); GRANT SELECT ON check_constraints TO PUBLIC; @@ -525,7 +525,7 @@ CREATE VIEW column_domain_usage AS AND a.attrelid = c.oid AND a.atttypid = t.oid AND t.typtype = 'd' - AND c.relkind IN ('r', 'v', 'f') + AND c.relkind IN ('r', 'v', 'f', 'P') AND a.attnum > 0 AND NOT a.attisdropped AND pg_has_role(t.typowner, 'USAGE'); @@ -564,7 +564,7 @@ CREATE VIEW column_privileges AS pr_c.relowner FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class - WHERE relkind IN ('r', 'v', 'f') + WHERE relkind IN ('r', 'v', 'f', 'P') ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable), pg_attribute a WHERE a.attrelid = pr_c.oid @@ -586,7 +586,7 @@ CREATE VIEW column_privileges AS ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable), pg_class c WHERE pr_a.attrelid = c.oid - AND relkind IN ('r', 'v', 'f') + AND relkind IN ('r', 'v', 'f', 'P') ) x, pg_namespace nc, pg_authid u_grantor, @@ -629,7 +629,7 @@ CREATE VIEW column_udt_usage AS WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND nc.oid = c.relnamespace - AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f') + AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f', 'P') AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE'); GRANT SELECT ON column_udt_usage TO PUBLIC; @@ -738,7 +738,7 @@ CREATE VIEW columns AS CAST('NEVER' AS character_data) AS is_generated, CAST(null AS character_data) AS generation_expression, - CAST(CASE WHEN c.relkind = 'r' OR + CAST(CASE WHEN c.relkind IN ('r', 'P') OR (c.relkind IN ('v', 'f') AND pg_column_is_updatable(c.oid, a.attnum, false)) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable @@ -753,7 +753,7 @@ CREATE VIEW columns AS WHERE (NOT pg_is_other_temp_schema(nc.oid)) - AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f') + AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f', 'P') AND (pg_has_role(c.relowner, 'USAGE') OR has_column_privilege(c.oid, a.attnum, @@ -789,7 +789,7 @@ CREATE VIEW constraint_column_usage AS AND d.objid = c.oid AND c.connamespace = nc.oid AND c.contype = 'c' - AND r.relkind = 'r' + AND r.relkind IN ('r', 'P') AND NOT a.attisdropped UNION ALL @@ -841,7 +841,7 @@ CREATE VIEW constraint_table_usage AS WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid AND ( (c.contype = 'f' AND c.confrelid = r.oid) OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) ) - AND r.relkind = 'r' + AND r.relkind IN ('r', 'P') AND pg_has_role(r.relowner, 'USAGE'); GRANT SELECT ON constraint_table_usage TO PUBLIC; @@ -1774,7 +1774,7 @@ CREATE VIEW table_constraints AS WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints - AND r.relkind = 'r' + AND r.relkind IN ('r', 'P') AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') -- SELECT privilege omitted, per SQL standard @@ -1804,7 +1804,7 @@ CREATE VIEW table_constraints AS AND a.attnotnull AND a.attnum > 0 AND NOT a.attisdropped - AND r.relkind = 'r' + AND r.relkind IN ('r', 'P') AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') -- SELECT privilege omitted, per SQL standard @@ -1854,7 +1854,7 @@ CREATE VIEW table_privileges AS ) AS grantee (oid, rolname) WHERE c.relnamespace = nc.oid - AND c.relkind IN ('r', 'v') + AND c.relkind IN ('r', 'v', 'P') AND c.grantee = grantee.oid AND c.grantor = u_grantor.oid AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER') @@ -1901,6 +1901,7 @@ CREATE VIEW tables AS WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' WHEN c.relkind = 'f' THEN 'FOREIGN TABLE' + WHEN c.relkind = 'P' THEN 'PARTITIONED TABLE' ELSE null END AS character_data) AS table_type, @@ -1911,7 +1912,7 @@ CREATE VIEW tables AS CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema, CAST(t.typname AS sql_identifier) AS user_defined_type_name, - CAST(CASE WHEN c.relkind = 'r' OR + CAST(CASE WHEN c.relkind IN ('r', 'P') OR (c.relkind IN ('v', 'f') AND -- 1 << CMD_INSERT pg_relation_is_updatable(c.oid, false) & 8 = 8) @@ -1923,7 +1924,7 @@ CREATE VIEW tables AS FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace) LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid) - WHERE c.relkind IN ('r', 'v', 'f') + WHERE c.relkind IN ('r', 'v', 'f', 'P') AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') @@ -2442,7 +2443,7 @@ CREATE VIEW view_column_usage AS AND dt.refclassid = 'pg_catalog.pg_class'::regclass AND dt.refobjid = t.oid AND t.relnamespace = nt.oid - AND t.relkind IN ('r', 'v', 'f') + AND t.relkind IN ('r', 'v', 'f', 'P') AND t.oid = a.attrelid AND dt.refobjsubid = a.attnum AND pg_has_role(t.relowner, 'USAGE'); @@ -2476,7 +2477,7 @@ CREATE VIEW view_routine_usage AS pg_depend dp, pg_proc p, pg_namespace np WHERE nv.oid = v.relnamespace - AND v.relkind = 'v' + AND v.relkind IN ('v') AND v.oid = dv.refobjid AND dv.refclassid = 'pg_catalog.pg_class'::regclass AND dv.classid = 'pg_catalog.pg_rewrite'::regclass @@ -2520,7 +2521,7 @@ CREATE VIEW view_table_usage AS AND dt.refclassid = 'pg_catalog.pg_class'::regclass AND dt.refobjid = t.oid AND t.relnamespace = nt.oid - AND t.relkind IN ('r', 'v', 'f') + AND t.relkind IN ('r', 'v', 'f', 'P') AND pg_has_role(t.relowner, 'USAGE'); GRANT SELECT ON view_table_usage TO PUBLIC; @@ -2673,7 +2674,7 @@ CREATE VIEW element_types AS a.attnum, a.atttypid, a.attcollation FROM pg_class c, pg_attribute a WHERE c.oid = a.attrelid - AND c.relkind IN ('r', 'v', 'f', 'c') + AND c.relkind IN ('r', 'v', 'f', 'c', 'P') AND attnum > 0 AND NOT attisdropped UNION ALL diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 31aade102b..de2a301566 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -136,7 +136,7 @@ CREATE VIEW pg_tables AS C.relrowsecurity AS rowsecurity FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) - WHERE C.relkind = 'r'; + WHERE C.relkind IN ('r', 'P'); CREATE VIEW pg_matviews AS SELECT @@ -285,6 +285,7 @@ SELECT WHEN rel.relkind = 'm' THEN 'materialized view'::text WHEN rel.relkind = 'S' THEN 'sequence'::text WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, + WHEN rel.relkind = 'P' THEN 'partitioned table'::text END AS objtype, rel.relnamespace AS objnamespace, CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident(rel.relname) -- 2.11.0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
