Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2DT9-0005rz-2n for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Mar 2026 19:19:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w2DT7-00CF6U-2X for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Mar 2026 19:19:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2DT7-00CF6J-1W for pgsql-hackers@lists.postgresql.org; Mon, 16 Mar 2026 19:19:14 +0000 Received: from mail-ot1-x334.google.com ([2607:f8b0:4864:20::334]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w2DT5-00000000Tbg-39Hv for pgsql-hackers@lists.postgresql.org; Mon, 16 Mar 2026 19:19:13 +0000 Received: by mail-ot1-x334.google.com with SMTP id 46e09a7af769-7d750eeaec3so1746188a34.0 for ; Mon, 16 Mar 2026 12:19:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773688750; x=1774293550; darn=lists.postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=lzutUkpsvENUegW86E0Vq/2xgD194XrH7S8dyaXjnu8=; b=QJQYiM1TFpR33hyRr9d0eynjxrQGjFTzkb8Sw775zIHvtx3RJht/19lrGLoAJOrb7M mGXaNxSi9ciMWZSpfSJp9mtpDZVfMcorRs9g0wT0f4E8SNlMdNW0koP7JrImQqt64xOv Pka4LXXT9AQN8Sll5n2SexCdkBw5cW70UDYGhI1Lm5JVBdb2QqIBUBP98KkLufcqfKwx JOvp+Jz64NZmbP2eeaZDHTiBjzvvZE54PgQhHj3IUOG4+pox/NSr5RvVkYPXgb/zmzy0 TMwjcIBrZrnGWp40a/O1D9S94xbeIJuTm3FafUWSJHtMRE88IUeTJ/uN+AgI6JF+fI1E +mWQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1773688750; x=1774293550; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-gg:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=lzutUkpsvENUegW86E0Vq/2xgD194XrH7S8dyaXjnu8=; b=H0MGCxsWySGHFCnT1HtWhKO9JJlc4zZiWbQlavLlEiXmPF4w+9rvTdE2euyK4WMRJ+ 7SHjzn8V0T+IareMc4NRLdx6lQRz3zTheZArtCk5oqIdqAZHAx5Pm4MTUweikI67AprW NDmZvAUqxTVz7XwxNJXyz/GP1LA7nN4VSUe0veCyk6yY2brn/bC5pTLoWKIxsd8Kxrp6 1Iew4s0Y+em0qVEpSm8o0g5TxUbF5fsjTfaguChVnul9OxFItqOlXqeVfLi+CL02ICEn 6ZQecK+xbeUQJdCvds6RITtndGg4+1Et+jupsb4xVFIxeZZl1xb77rjiaabBgTs2Yxsi fLrQ== X-Forwarded-Encrypted: i=1; AJvYcCWI79ADmYm0W73BpEdA7RpTJKeRfaT1xzGeO6PMUan4Aoh6fMWbJrACQUbuPMqJhMg9cCATwLv8qHMBQv7c@lists.postgresql.org X-Gm-Message-State: AOJu0Yz9B9GHGshn1fDAaMgoOqev5sCiAl36gPG1vfkirVWTvoiRpf1N /fzUz7Y5ZDqCtoU1h5qR8KBvOPhUHh/3apGJ7ZkBDTadA3ms7iYFn6emhfKd5A== X-Gm-Gg: ATEYQzwCWAX6GSVWsGoHf0xsb9QqEaroULesgXpqaVnREY9kHw+Gs9mS/4pqdz18pKE 73vdY++lCKcjeujUxbfTAsvQOxP+tZ0/GFPO9Z9dyIW+DX4tsUpJjHTcpsk0xvnvoFvXvursFNV x2iAawAKDe0WO1l3cr8FbBuswxZSx3sImclLdTH/hhHtjK+amW6tUaGsvoMl6uJKNPy0yg4NcFM YbglgeKW2cTqJEdDaS9VOuJ/ZtzPQ6MX5G9MnGLhTYznFCftXAvbm7kRzUQ2/1uEdKBSaEck4K9 rsPKcOz3GxTaLTBokWraDKIcX2dkvmPOm03neSRrMB7p6phRLJtWNX87Otkm+eaP13mA5Yvr1Uw CPFHYi54O51BcOPcfryVXYjTaBYNIaCS/9pej2NTST60DSihXDpEJi14d/O4ZPGbzzAfBMWxmKG RtTBbNrytZh4qmovoBuc4aW5wLIIdIIVqwSoS5uyallknNoQ7J91bEppNmLJqhwvCk/TxmT5Ynl xVs0OdJKs9xwhdFWao538XGjHFuJCrE X-Received: by 2002:a05:6830:4c07:b0:7d7:54d5:c6f8 with SMTP id 46e09a7af769-7d78250c8cemr9765894a34.17.1773688750054; Mon, 16 Mar 2026 12:19:10 -0700 (PDT) Received: from nathan (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id 46e09a7af769-7d76ac8d43esm14111534a34.10.2026.03.16.12.19.08 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 16 Mar 2026 12:19:09 -0700 (PDT) Date: Mon, 16 Mar 2026 14:19:07 -0500 From: Nathan Bossart To: Corey Huinker Cc: Sami Imseih , pgsql-hackers@lists.postgresql.org Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="tOwyRy8JMBwx/S6s" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --tOwyRy8JMBwx/S6s Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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. > + tableid oid > + (references pg_attribute.attrelid) 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. > + tableid oid > + (references pg_statistic_ext.stxrelid) ... and here. > + tableid oid > + (references pg_statistic_ext.stxrelid) ... and here. -- nathan --tOwyRy8JMBwx/S6s Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v9-0001-Add-OIDs-and-attribute-numbers-to-pg_stats-and-fr.patch From 58c0420a207e6ee6543a5f3f11f85212109f07d7 Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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 + + + tableid oid + (references pg_class.oid) + + + OID of table + + + attname name @@ -4424,6 +4434,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + attnum int2 + (references pg_attribute>.attnum) + + + Number of column described by this row + + + inherited bool @@ -4666,6 +4686,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + tableid oid + (references pg_class.oid) + + + OID of table + + + statistics_schemaname name @@ -4686,6 +4716,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + statistics_id oid + (references pg_statistic_ext.oid) + + + OID of extended statistics object + + + statistics_owner name @@ -4877,6 +4917,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + tableid oid + (references pg_class.oid) + + + OID of table the statistics object is defined on + + + statistics_schemaname name @@ -4897,6 +4947,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + + statistics_id oid + (references pg_statistic_ext.oid) + + + OID of extended statistics object + + + statistics_owner name 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) --tOwyRy8JMBwx/S6s--