On Sat, Mar 14, 2026 at 05:13:19PM -0400, Corey Huinker wrote: > "_stable", nice name choice. Rebasing was nonzero but just barely.
Thanks. Here is what I have staged for commit for the next patch in the series. > CREATE VIEW pg_stats WITH (security_barrier) AS > SELECT > - nspname AS schemaname, > - relname AS tablename, > - attname AS attname, > + n.nspname AS schemaname, > + c.relname AS tablename, > + a.attrelid AS tableid, > + a.attname AS attname, > + a.attnum AS attnum, I didn't see why we needed to change the lines for the existing columns, so I left those parts out. > 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, I went with "statistics_id" to match the naming scheme. > 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, I left the expr_attnum stuff out. It seems to make this patch quite large and complicated, we don't plan to use it for the pg_dump patch, and I'm not sure about showing users a "synthetic attnum" that seems to have no other point of reference. Would this information be useful in pg_dump somewhere? I'm curious to hear more about the intent. > CREATE VIEW stats_import.pg_stats_stable AS > - SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, > + SELECT schemaname, tablename, attname, attnum, inherited, null_frac, > avg_width, I didn't see much value in adding attnum here given the size of the changes to the expected output it produces. > + <structfield>tableid</structfield> <type>oid</type> > + (references <link > linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attrelid</structfield>) While we might be pulling the OID from pg_attribute in the view, we seem to point to the true origin for these reference notes elsewhere, so I changed it to pg_class.oid here. > + <structfield>tableid</structfield> <type>oid</type> > + (references <link > linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>stxrelid</structfield>) ... and here. > + <structfield>tableid</structfield> <type>oid</type> > + (references <link > linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>stxrelid</structfield>) ... and here. -- nathan
>From 58c0420a207e6ee6543a5f3f11f85212109f07d7 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Mon, 16 Mar 2026 14:09:54 -0500 Subject: [PATCH v9 1/1] Add OIDs and attribute numbers to pg_stats and friends. XXX: NEEDS CATVERSION BUMP --- doc/src/sgml/system-views.sgml | 60 ++++++++++++++++++++++ src/backend/catalog/system_views.sql | 6 +++ src/test/regress/expected/rules.out | 6 +++ src/test/regress/expected/stats_import.out | 4 +- 4 files changed, 74 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index e5fe423fc61..7f2c8d1713a 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -4414,6 +4414,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tableid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>) + </para> + <para> + OID of table + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>attname</structfield> <type>name</type> @@ -4424,6 +4434,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>attnum</structfield> <type>int2</type> + (references <link linkend="catalog-pg-attribute"><structname>pg_attribute></structname></link>.<structfield>attnum</structfield>) + </para> + <para> + Number of column described by this row + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>inherited</structfield> <type>bool</type> @@ -4666,6 +4686,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tableid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>) + </para> + <para> + OID of table + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_schemaname</structfield> <type>name</type> @@ -4686,6 +4716,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>statistics_id</structfield> <type>oid</type> + (references <link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>oid</structfield>) + </para> + <para> + OID of extended statistics object + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_owner</structfield> <type>name</type> @@ -4877,6 +4917,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tableid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>) + </para> + <para> + OID of table the statistics object is defined on + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_schemaname</structfield> <type>name</type> @@ -4897,6 +4947,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>statistics_id</structfield> <type>oid</type> + (references <link linkend="catalog-pg-statistic-ext"><structname>pg_statistic_ext</structname></link>.<structfield>oid</structfield>) + </para> + <para> + OID of extended statistics object + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>statistics_owner</structfield> <type>name</type> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 6d6dce18fa3..f1ed7b58f13 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -191,7 +191,9 @@ CREATE VIEW pg_stats WITH (security_barrier) AS SELECT nspname AS schemaname, relname AS tablename, + attrelid AS tableid, attname AS attname, + attnum, stainherit AS inherited, stanullfrac AS null_frac, stawidth AS avg_width, @@ -278,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 statistics_id, pg_get_userbyid(s.stxowner) AS statistics_owner, ( SELECT array_agg(a.attname ORDER BY a.attnum) FROM unnest(s.stxkeys) k @@ -312,8 +316,10 @@ 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 statistics_id, pg_get_userbyid(s.stxowner) AS statistics_owner, stat.expr, sd.stxdinherit AS inherited, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 9ed0a1756c0..32bea58db2c 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2546,7 +2546,9 @@ pg_statio_user_tables| SELECT relid, WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stats| SELECT n.nspname AS schemaname, c.relname AS tablename, + a.attrelid AS tableid, a.attname, + a.attnum, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, @@ -2638,8 +2640,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 statistics_id, 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) @@ -2666,8 +2670,10 @@ 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 statistics_id, pg_get_userbyid(s.stxowner) AS statistics_owner, stat.expr, sd.stxdinherit AS inherited, diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 0aa9f657376..fd660791ea9 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -78,7 +78,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. @@ -119,7 +119,7 @@ SELECT COUNT(*) FROM pg_attribute attnum > 0; count ------- - 20 + 22 (1 row) -- Create a view that is used purely for the type based on pg_stats_ext_exprs. -- 2.50.1 (Apple Git-155)
