From 5a0be7b5f683da6e8636fcc6cba830227b16c358 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Mon, 9 Mar 2026 16:53:43 -0400 Subject: [PATCH v4 3/3] Add tableid, statid, expr_attnum columns to pg_stats_ext[_exprs]. This mimics the work done in the previous patch adding the columns tableid and statid to both pg_stats_ext and pg_stats_ext_exprs. It also adds expr_attnum to pg_stats_ext_exprs. There is no present need for these additional columns, but if and when pg_dump starts fetching extended statistics object stats in bulk, they will be available. --- src/backend/catalog/system_views.sql | 163 +++++++++++---------- src/test/regress/expected/rules.out | 119 ++++++++------- src/test/regress/expected/stats_import.out | 22 ++- src/test/regress/sql/stats_import.sql | 8 + doc/src/sgml/system-views.sgml | 52 +++++++ 5 files changed, 227 insertions(+), 137 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 70846f1555d..09274eac9c8 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -280,8 +280,10 @@ 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, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, ( SELECT array_agg(a.attname ORDER BY a.attnum) FROM unnest(s.stxkeys) k @@ -314,92 +316,97 @@ CREATE VIEW pg_stats_ext WITH (security_barrier) AS CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS SELECT cn.nspname AS schemaname, c.relname AS tablename, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, - stat.expr, + expr.expr, + 0 - expr.ordinality AS expr_attnum, sd.stxdinherit AS inherited, - (stat.a).stanullfrac AS null_frac, - (stat.a).stawidth AS avg_width, - (stat.a).stadistinct AS n_distinct, - (CASE - WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5 - END) AS most_common_vals, - (CASE - WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1 - WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2 - WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3 - WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4 - WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5 - END) AS most_common_freqs, - (CASE - WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5 - END) AS histogram_bounds, - (CASE - WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1] - WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1] - WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1] - WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1] - WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1] - END) correlation, - (CASE - WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5 - END) AS most_common_elems, - (CASE - WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1 - WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2 - WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3 - WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4 - WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5 - END) AS most_common_elem_freqs, - (CASE - WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1 - WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2 - WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3 - WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4 - WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5 - END) AS elem_count_histogram, - (CASE - WHEN (stat.a).stakind1 = 6 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 6 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 6 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 6 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 6 THEN (stat.a).stavalues5 - END) AS range_length_histogram, - (CASE - WHEN (stat.a).stakind1 = 6 THEN (stat.a).stanumbers1[1] - WHEN (stat.a).stakind2 = 6 THEN (stat.a).stanumbers2[1] - WHEN (stat.a).stakind3 = 6 THEN (stat.a).stanumbers3[1] - WHEN (stat.a).stakind4 = 6 THEN (stat.a).stanumbers4[1] - WHEN (stat.a).stakind5 = 6 THEN (stat.a).stanumbers5[1] - END) AS range_empty_frac, - (CASE - WHEN (stat.a).stakind1 = 7 THEN (stat.a).stavalues1 - WHEN (stat.a).stakind2 = 7 THEN (stat.a).stavalues2 - WHEN (stat.a).stakind3 = 7 THEN (stat.a).stavalues3 - WHEN (stat.a).stakind4 = 7 THEN (stat.a).stavalues4 - WHEN (stat.a).stakind5 = 7 THEN (stat.a).stavalues5 - END) AS range_bounds_histogram + a.stanullfrac AS null_frac, + a.stawidth AS avg_width, + a.stadistinct AS n_distinct, + CASE + WHEN a.stakind1 = 1 THEN a.stavalues1 + WHEN a.stakind2 = 1 THEN a.stavalues2 + WHEN a.stakind3 = 1 THEN a.stavalues3 + WHEN a.stakind4 = 1 THEN a.stavalues4 + WHEN a.stakind5 = 1 THEN a.stavalues5 + END AS most_common_vals, + CASE + WHEN a.stakind1 = 1 THEN a.stanumbers1 + WHEN a.stakind2 = 1 THEN a.stanumbers2 + WHEN a.stakind3 = 1 THEN a.stanumbers3 + WHEN a.stakind4 = 1 THEN a.stanumbers4 + WHEN a.stakind5 = 1 THEN a.stanumbers5 + END AS most_common_freqs, + CASE + WHEN a.stakind1 = 2 THEN a.stavalues1 + WHEN a.stakind2 = 2 THEN a.stavalues2 + WHEN a.stakind3 = 2 THEN a.stavalues3 + WHEN a.stakind4 = 2 THEN a.stavalues4 + WHEN a.stakind5 = 2 THEN a.stavalues5 + END AS histogram_bounds, + CASE + WHEN a.stakind1 = 3 THEN a.stanumbers1[1] + WHEN a.stakind2 = 3 THEN a.stanumbers2[1] + WHEN a.stakind3 = 3 THEN a.stanumbers3[1] + WHEN a.stakind4 = 3 THEN a.stanumbers4[1] + WHEN a.stakind5 = 3 THEN a.stanumbers5[1] + END AS correlation, + CASE + WHEN a.stakind1 = 4 THEN a.stavalues1 + WHEN a.stakind2 = 4 THEN a.stavalues2 + WHEN a.stakind3 = 4 THEN a.stavalues3 + WHEN a.stakind4 = 4 THEN a.stavalues4 + WHEN a.stakind5 = 4 THEN a.stavalues5 + END aS most_common_elems, + CASE + WHEN a.stakind1 = 4 THEN a.stanumbers1 + WHEN a.stakind2 = 4 THEN a.stanumbers2 + WHEN a.stakind3 = 4 THEN a.stanumbers3 + WHEN a.stakind4 = 4 THEN a.stanumbers4 + WHEN a.stakind5 = 4 THEN a.stanumbers5 + END aS most_common_elem_freqs, + CASE + WHEN a.stakind1 = 5 THEN a.stanumbers1 + WHEN a.stakind2 = 5 THEN a.stanumbers2 + WHEN a.stakind3 = 5 THEN a.stanumbers3 + WHEN a.stakind4 = 5 THEN a.stanumbers4 + WHEN a.stakind5 = 5 THEN a.stanumbers5 + END aS elem_count_histogram, + CASE + WHEN a.stakind1 = 6 THEN a.stavalues1 + WHEN a.stakind2 = 6 THEN a.stavalues2 + WHEN a.stakind3 = 6 THEN a.stavalues3 + WHEN a.stakind4 = 6 THEN a.stavalues4 + WHEN a.stakind5 = 6 THEN a.stavalues5 + END aS range_length_histogram, + CASE + WHEN a.stakind1 = 6 THEN a.stanumbers1[1] + WHEN a.stakind2 = 6 THEN a.stanumbers2[1] + WHEN a.stakind3 = 6 THEN a.stanumbers3[1] + WHEN a.stakind4 = 6 THEN a.stanumbers4[1] + WHEN a.stakind5 = 6 THEN a.stanumbers5[1] + END aS range_empty_frac, + CASE + WHEN a.stakind1 = 7 THEN a.stavalues1 + WHEN a.stakind2 = 7 THEN a.stavalues2 + WHEN a.stakind3 = 7 THEN a.stavalues3 + WHEN a.stakind4 = 7 THEN a.stavalues4 + WHEN a.stakind5 = 7 THEN a.stavalues5 + END AS range_bounds_histogram FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid) LEFT 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) - JOIN LATERAL ( - SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr, - unnest(sd.stxdexpr)::pg_statistic AS a - ) stat ON (stat.expr IS NOT NULL) + JOIN LATERAL unnest(pg_get_statisticsobjdef_expressions(s.oid)) + WITH ORDINALITY AS expr(expr, ordinality) + ON s.stxexprs IS NOT NULL + LEFT JOIN LATERAL unnest(sd.stxdexpr) + WITH ORDINALITY AS a + ON a.ordinality = expr.ordinality WHERE pg_has_role(c.relowner, 'USAGE') AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 46be5c7ac90..eab38a43609 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2618,8 +2618,10 @@ pg_stats| SELECT n.nspname AS schemaname, 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, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg FROM (unnest(s.stxkeys) k(k) @@ -2646,101 +2648,104 @@ pg_stats_ext| SELECT cn.nspname AS schemaname, WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_stats_ext_exprs| SELECT cn.nspname AS schemaname, c.relname AS tablename, + s.stxrelid AS tableid, sn.nspname AS statistics_schemaname, s.stxname AS statistics_name, + s.oid AS statid, pg_get_userbyid(s.stxowner) AS statistics_owner, - stat.expr, + expr.expr, + (0 - expr.ordinality) AS expr_attnum, sd.stxdinherit AS inherited, - (stat.a).stanullfrac AS null_frac, - (stat.a).stawidth AS avg_width, - (stat.a).stadistinct AS n_distinct, + a.stanullfrac AS null_frac, + a.stawidth AS avg_width, + a.stadistinct AS n_distinct, CASE - WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 1) THEN a.stavalues1 + WHEN (a.stakind2 = 1) THEN a.stavalues2 + WHEN (a.stakind3 = 1) THEN a.stavalues3 + WHEN (a.stakind4 = 1) THEN a.stavalues4 + WHEN (a.stakind5 = 1) THEN a.stavalues5 ELSE NULL::anyarray END AS most_common_vals, CASE - WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stanumbers1 - WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stanumbers2 - WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stanumbers3 - WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stanumbers4 - WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stanumbers5 + WHEN (a.stakind1 = 1) THEN a.stanumbers1 + WHEN (a.stakind2 = 1) THEN a.stanumbers2 + WHEN (a.stakind3 = 1) THEN a.stanumbers3 + WHEN (a.stakind4 = 1) THEN a.stanumbers4 + WHEN (a.stakind5 = 1) THEN a.stanumbers5 ELSE NULL::real[] END AS most_common_freqs, CASE - WHEN ((stat.a).stakind1 = 2) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 2) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 2) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 2) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 2) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 2) THEN a.stavalues1 + WHEN (a.stakind2 = 2) THEN a.stavalues2 + WHEN (a.stakind3 = 2) THEN a.stavalues3 + WHEN (a.stakind4 = 2) THEN a.stavalues4 + WHEN (a.stakind5 = 2) THEN a.stavalues5 ELSE NULL::anyarray END AS histogram_bounds, CASE - WHEN ((stat.a).stakind1 = 3) THEN (stat.a).stanumbers1[1] - WHEN ((stat.a).stakind2 = 3) THEN (stat.a).stanumbers2[1] - WHEN ((stat.a).stakind3 = 3) THEN (stat.a).stanumbers3[1] - WHEN ((stat.a).stakind4 = 3) THEN (stat.a).stanumbers4[1] - WHEN ((stat.a).stakind5 = 3) THEN (stat.a).stanumbers5[1] + WHEN (a.stakind1 = 3) THEN a.stanumbers1[1] + WHEN (a.stakind2 = 3) THEN a.stanumbers2[1] + WHEN (a.stakind3 = 3) THEN a.stanumbers3[1] + WHEN (a.stakind4 = 3) THEN a.stanumbers4[1] + WHEN (a.stakind5 = 3) THEN a.stanumbers5[1] ELSE NULL::real END AS correlation, CASE - WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 4) THEN a.stavalues1 + WHEN (a.stakind2 = 4) THEN a.stavalues2 + WHEN (a.stakind3 = 4) THEN a.stavalues3 + WHEN (a.stakind4 = 4) THEN a.stavalues4 + WHEN (a.stakind5 = 4) THEN a.stavalues5 ELSE NULL::anyarray END AS most_common_elems, CASE - WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stanumbers1 - WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stanumbers2 - WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stanumbers3 - WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stanumbers4 - WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stanumbers5 + WHEN (a.stakind1 = 4) THEN a.stanumbers1 + WHEN (a.stakind2 = 4) THEN a.stanumbers2 + WHEN (a.stakind3 = 4) THEN a.stanumbers3 + WHEN (a.stakind4 = 4) THEN a.stanumbers4 + WHEN (a.stakind5 = 4) THEN a.stanumbers5 ELSE NULL::real[] END AS most_common_elem_freqs, CASE - WHEN ((stat.a).stakind1 = 5) THEN (stat.a).stanumbers1 - WHEN ((stat.a).stakind2 = 5) THEN (stat.a).stanumbers2 - WHEN ((stat.a).stakind3 = 5) THEN (stat.a).stanumbers3 - WHEN ((stat.a).stakind4 = 5) THEN (stat.a).stanumbers4 - WHEN ((stat.a).stakind5 = 5) THEN (stat.a).stanumbers5 + WHEN (a.stakind1 = 5) THEN a.stanumbers1 + WHEN (a.stakind2 = 5) THEN a.stanumbers2 + WHEN (a.stakind3 = 5) THEN a.stanumbers3 + WHEN (a.stakind4 = 5) THEN a.stanumbers4 + WHEN (a.stakind5 = 5) THEN a.stanumbers5 ELSE NULL::real[] END AS elem_count_histogram, CASE - WHEN ((stat.a).stakind1 = 6) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 6) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 6) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 6) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 6) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 6) THEN a.stavalues1 + WHEN (a.stakind2 = 6) THEN a.stavalues2 + WHEN (a.stakind3 = 6) THEN a.stavalues3 + WHEN (a.stakind4 = 6) THEN a.stavalues4 + WHEN (a.stakind5 = 6) THEN a.stavalues5 ELSE NULL::anyarray END AS range_length_histogram, CASE - WHEN ((stat.a).stakind1 = 6) THEN (stat.a).stanumbers1[1] - WHEN ((stat.a).stakind2 = 6) THEN (stat.a).stanumbers2[1] - WHEN ((stat.a).stakind3 = 6) THEN (stat.a).stanumbers3[1] - WHEN ((stat.a).stakind4 = 6) THEN (stat.a).stanumbers4[1] - WHEN ((stat.a).stakind5 = 6) THEN (stat.a).stanumbers5[1] + WHEN (a.stakind1 = 6) THEN a.stanumbers1[1] + WHEN (a.stakind2 = 6) THEN a.stanumbers2[1] + WHEN (a.stakind3 = 6) THEN a.stanumbers3[1] + WHEN (a.stakind4 = 6) THEN a.stanumbers4[1] + WHEN (a.stakind5 = 6) THEN a.stanumbers5[1] ELSE NULL::real END AS range_empty_frac, CASE - WHEN ((stat.a).stakind1 = 7) THEN (stat.a).stavalues1 - WHEN ((stat.a).stakind2 = 7) THEN (stat.a).stavalues2 - WHEN ((stat.a).stakind3 = 7) THEN (stat.a).stavalues3 - WHEN ((stat.a).stakind4 = 7) THEN (stat.a).stavalues4 - WHEN ((stat.a).stakind5 = 7) THEN (stat.a).stavalues5 + WHEN (a.stakind1 = 7) THEN a.stavalues1 + WHEN (a.stakind2 = 7) THEN a.stavalues2 + WHEN (a.stakind3 = 7) THEN a.stavalues3 + WHEN (a.stakind4 = 7) THEN a.stavalues4 + WHEN (a.stakind5 = 7) THEN a.stavalues5 ELSE NULL::anyarray END AS range_bounds_histogram - FROM (((((pg_statistic_ext s + FROM ((((((pg_statistic_ext s JOIN pg_class c ON ((c.oid = s.stxrelid))) LEFT 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))) - JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr, - unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL))) + JOIN LATERAL unnest(pg_get_statisticsobjdef_expressions(s.oid)) WITH ORDINALITY expr(expr, ordinality) ON ((s.stxexprs IS NOT NULL))) + LEFT JOIN LATERAL unnest(sd.stxdexpr) WITH ORDINALITY a(starelid, staattnum, stainherit, stanullfrac, stawidth, stadistinct, stakind1, stakind2, stakind3, stakind4, stakind5, staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, stavalues1, stavalues2, stavalues3, stavalues4, stavalues5, ordinality) ON ((a.ordinality = expr.ordinality))) WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_tables| SELECT n.nspname AS schemaname, c.relname AS tablename, diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 8d66d4d9329..68e1b75f5e2 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -66,7 +66,7 @@ SELECT COUNT(*) FROM pg_attribute attnum > 0; count ------- - 15 + 17 (1 row) -- Create a view that is used purely for the type based on pg_stats_ext. @@ -107,7 +107,7 @@ SELECT COUNT(*) FROM pg_attribute attnum > 0; count ------- - 20 + 23 (1 row) -- Create a view that is used purely for the type based on pg_stats_ext_exprs. @@ -1684,6 +1684,8 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e -------+----------- (0 rows) +-- A NULL value on inherited makes sense here because the rows are expanded +-- from pg_statistic_ext but the values come from the empty pg_statistic_ext_data. SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'test_stat' GROUP BY e.inherited; @@ -1701,6 +1703,14 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e 1 | t (1 row) +SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'part_parent_stat' GROUP BY e.inherited; + count | inherited +-------+----------- + 1 | t +(1 row) + SELECT pg_catalog.pg_clear_extended_stats( schemaname => 'stats_import', relname => 'part_parent', @@ -1719,6 +1729,14 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e -------+----------- (0 rows) +SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'part_parent_stat' GROUP BY e.inherited; + count | inherited +-------+----------- + 1 | +(1 row) + -- Check that MAINTAIN is required when clearing statistics. CREATE ROLE regress_test_extstat_clear; GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_clear; diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 8fbec83b5e0..081ff936fd8 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1268,6 +1268,8 @@ COMMIT; SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'test_stat' GROUP BY e.inherited; +-- A NULL value on inherited makes sense here because the rows are expanded +-- from pg_statistic_ext but the values come from the empty pg_statistic_ext_data. SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'test_stat' GROUP BY e.inherited; @@ -1275,6 +1277,9 @@ SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'part_parent_stat' GROUP BY e.inherited; +SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'part_parent_stat' GROUP BY e.inherited; SELECT pg_catalog.pg_clear_extended_stats( schemaname => 'stats_import', relname => 'part_parent', @@ -1284,6 +1289,9 @@ SELECT pg_catalog.pg_clear_extended_stats( SELECT COUNT(*), e.inherited FROM pg_stats_ext AS e WHERE e.statistics_schemaname = 'stats_import' AND e.statistics_name = 'part_parent_stat' GROUP BY e.inherited; +SELECT COUNT(*), e.inherited FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'part_parent_stat' GROUP BY e.inherited; -- Check that MAINTAIN is required when clearing statistics. CREATE ROLE regress_test_extstat_clear; diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 8214f3cd062..25a013abbf8 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -4686,6 +4686,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + tableid oid + (references pg_statistic_ext.stxrelid) + + + ID of the table + + + statistics_schemaname name @@ -4706,6 +4716,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + statid oid + (references pg_statistic_ext.oid) + + + ID of the extended statistics object + + + statistics_owner name @@ -4897,6 +4917,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + tableid oid + (references pg_statistic_ext.stxrelid) + + + ID of the table + + + statistics_schemaname name @@ -4917,6 +4947,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + statid oid + (references pg_statistic_ext.oid) + + + ID of the extended statistics object + + + statistics_owner name @@ -4936,6 +4976,18 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + expr_attnum int2 + + + Synthetic attnum used to reference this expression in + pg_statistic_ext.stxdndistinct + and + pg_statistic_ext.stxddependencies + + + inherited bool -- 2.53.0