agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathandbossart@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump
Date: Mon, 16 Mar 2026 14:19:07 -0500
Message-ID: <abhXqx-xffsknv3B@nathan> (raw)
In-Reply-To: <CADkLM=cfpFMpgOGPEyr7U+-Cxzvukap-_s8p-ijMTyRphP=oeg@mail.gmail.com>
References: <aa81xZp421DSkHiW@nathan>
<CADkLM=dhYeJtP+8h8k4ULCu-P1pNHmFyDFBm8B0WU6A6i+NgPQ@mail.gmail.com>
<abBIu0lYyFYAq5fx@nathan>
<CADkLM=d-=_hbOUsMc4u_ScLmSNox6EbzFCmaMn7MTh6f_SLU_A@mail.gmail.com>
<CADkLM=d-yUR8JsM-UmCfeRAhVj+X9MYi0kz9=zQO4EoBkd8H8A@mail.gmail.com>
<CAA5RZ0tiL01rYm7=WaDGLUYjvz+fQRqmaXyRUv9jKRnR3_6xDQ@mail.gmail.com>
<CADkLM=fn=RmBD0-h+yjTxhpLtbK9s_QdvT-HsGtUj+-1N=6s0g@mail.gmail.com>
<CAA5RZ0sf7vYav+Q5GwxGikBZSYUm+4haA5bHC0N67ct570HLcg@mail.gmail.com>
<abRugSlNTmW0-9Um@nathan>
<CADkLM=cfpFMpgOGPEyr7U+-Cxzvukap-_s8p-ijMTyRphP=oeg@mail.gmail.com>
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 <nathan@postgresql.org>
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)
Attachments:
[text/plain] v9-0001-Add-OIDs-and-attribute-numbers-to-pg_stats-and-fr.patch (8.4K, ../abhXqx-xffsknv3B@nathan/2-v9-0001-Add-OIDs-and-attribute-numbers-to-pg_stats-and-fr.patch)
download | inline diff:
From 58c0420a207e6ee6543a5f3f11f85212109f07d7 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
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)
view thread (40+ messages) latest in thread
Message-ID: <abhXqx-xffsknv3B@nathan>
Permalink: ../abhXqx-xffsknv3B@nathan/
Also on: postgresql.org/message-id/abhXqx-xffsknv3B@nathan
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: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, corey.huinker@gmail.com, samimseih@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump
In-Reply-To: <abhXqx-xffsknv3B@nathan>
* 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