agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
Subject: [PATCH 3/3] add pg_stats_ext view
Date: Thu, 6 Jun 2019 18:40:50 +0200
---
src/backend/catalog/system_views.sql | 41 ++++++++++++++++++++++++++++
src/test/regress/expected/rules.out | 29 ++++++++++++++++++++
2 files changed, 70 insertions(+)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 78a103cdb9..5bcf24ee84 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -253,6 +253,47 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
REVOKE ALL on pg_statistic FROM public;
+CREATE VIEW pg_stats_ext WITH (security_barrier) AS
+ SELECT cn.nspname AS schemaname,
+ c.relname AS tablename,
+ sn.nspname AS statistics_schemaname,
+ s.stxname AS statistics_name,
+ pg_get_userbyid(c.relowner) AS statistics_owner,
+ ( SELECT array_agg(a.attname ORDER BY a.attnum)
+ FROM unnest(s.stxkeys) k
+ JOIN pg_attribute a
+ ON (a.attrelid = s.stxrelid AND a.attnum = k)
+ ) AS attnames,
+ s.stxkind AS kinds,
+ sd.stxndistinct AS n_distinct,
+ sd.stxdependencies AS dependencies,
+ m.most_common_vals,
+ m.most_common_val_nulls,
+ m.most_common_freqs,
+ m.most_common_base_freqs
+ FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
+ JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
+ LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
+ LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
+ LEFT JOIN LATERAL
+ ( SELECT array_agg(values) AS most_common_vals,
+ array_agg(nulls) AS most_common_val_nulls,
+ array_agg(frequency) AS most_common_freqs,
+ array_agg(base_frequency) AS most_common_base_freqs
+ FROM pg_mcv_list_items(sd.stxmcv)
+ ) m ON sd.stxmcv IS NOT NULL
+ WHERE NOT EXISTS
+ ( SELECT 1
+ FROM unnest(stxkeys) k
+ JOIN pg_attribute a
+ ON (a.attrelid = s.stxrelid AND a.attnum = k)
+ WHERE NOT has_column_privilege(c.oid, a.attnum, 'select') )
+ AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
+
+REVOKE ALL on pg_statistic_ext FROM public;
+GRANT SELECT (oid, stxrelid, stxname, stxnamespace, stxowner, stxkeys, stxkind)
+ ON pg_statistic_ext TO public;
+
CREATE VIEW pg_publication_tables AS
SELECT
P.pubname AS pubname,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7d365c48d1..80faf46648 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2284,6 +2284,35 @@ pg_stats| SELECT n.nspname AS schemaname,
JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
+pg_stats_ext| SELECT cn.nspname AS schemaname,
+ c.relname AS tablename,
+ sn.nspname AS statistics_schemaname,
+ s.stxname AS statistics_name,
+ pg_get_userbyid(c.relowner) AS statistics_owner,
+ ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
+ FROM (unnest(s.stxkeys) k(k)
+ JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))) AS attnames,
+ s.stxkind AS kinds,
+ sd.stxndistinct AS n_distinct,
+ sd.stxdependencies AS dependencies,
+ m.most_common_vals,
+ m.most_common_val_nulls,
+ m.most_common_freqs,
+ m.most_common_base_freqs
+ FROM (((((pg_statistic_ext s
+ JOIN pg_class c ON ((c.oid = s.stxrelid)))
+ JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
+ LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
+ LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
+ LEFT JOIN LATERAL ( SELECT array_agg(pg_mcv_list_items."values") AS most_common_vals,
+ array_agg(pg_mcv_list_items.nulls) AS most_common_val_nulls,
+ array_agg(pg_mcv_list_items.frequency) AS most_common_freqs,
+ array_agg(pg_mcv_list_items.base_frequency) AS most_common_base_freqs
+ FROM pg_mcv_list_items(sd.stxmcv) pg_mcv_list_items(index, "values", nulls, frequency, base_frequency)) m ON ((sd.stxmcv IS NOT NULL)))
+ WHERE ((NOT (EXISTS ( SELECT 1
+ FROM (unnest(s.stxkeys) k(k)
+ JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))
+ WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
pg_tables| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pg_get_userbyid(c.relowner) AS tableowner,
--
2.20.1
--l2nmj2zxg7aod3jr--
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH 3/3] add pg_stats_ext view
In-Reply-To: <no-message-id-213015@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox