On Thu, Aug 21, 2025 at 9:44 AM Nathan Bossart <nathandboss...@gmail.com> wrote:
> On Thu, Aug 21, 2025 at 03:19:40AM -0400, Corey Huinker wrote: > > Assuming that I'm not missing something, the fix seems straightforward. > > I'll set about coding it up tomorrow if nobody has done so by then. > > I've added an open item for this. Here's the query changes, no regression test just yet.
From 62d41900ff0938472e808bf2a87e35d294717698 Mon Sep 17 00:00:00 2001 From: Corey Huinker <corey.huin...@gmail.com> Date: Thu, 21 Aug 2025 12:30:17 -0400 Subject: [PATCH v1] Have missing-stats query use security barrier views. Previously, the missing-stats query used pg_statistic and pg_statistic_ext_data, which meant that the queries would fail for non-superusers like pg_maintain as reported by Fujii Masao. --- src/bin/scripts/vacuumdb.c | 47 +++++++++++++++++++++++--------------- 1 file changed, 29 insertions(+), 18 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 22093e50aa5..919c77035a1 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -973,35 +973,42 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n" " AND NOT a.attisdropped\n" " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" - " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" - " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" - " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats s\n" + " WHERE s.schemaname OPERATOR(pg_catalog.=) ns.nspname\n" + " AND s.tablename OPERATOR(pg_catalog.=) c.relname\n" + " AND s.attname OPERATOR(pg_catalog.=) a.attname\n" + " AND s.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* extended stats */ appendPQExpBufferStr(&catalog_query, " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n" + " JOIN pg_catalog.pg_namespace en" + " ON en.oid OPERATOR(pg_catalog.=) e.stxnamespace\n" " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n" " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n" - " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n" - " AND d.stxdinherit OPERATOR(pg_catalog.=) p.inherited))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats_ext d\n" + " WHERE d.statistics_schemaname OPERATOR(pg_catalog.=) en.nspname\n" + " AND d.statistics_name OPERATOR(pg_catalog.=) e.stxname\n" + " AND d.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* expression indexes */ appendPQExpBufferStr(&catalog_query, " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n" " JOIN pg_catalog.pg_index i" " ON i.indexrelid OPERATOR(pg_catalog.=) a.attrelid\n" + " JOIN pg_catalog.pg_class ic" + " ON ic.oid OPERATOR(pg_catalog.=) i.indexrelid\n" " WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n" " AND i.indkey[a.attnum OPERATOR(pg_catalog.-) 1::pg_catalog.int2]" " OPERATOR(pg_catalog.=) 0::pg_catalog.int2\n" " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n" " AND NOT a.attisdropped\n" " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" - " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" - " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" - " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats s\n" + " WHERE s.schemaname OPERATOR(pg_catalog.=) ns.nspname\n" + " AND s.tablename OPERATOR(pg_catalog.=) ic.relname\n" + " AND s.attname OPERATOR(pg_catalog.=) a.attname\n" + " AND s.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* inheritance and regular stats */ appendPQExpBufferStr(&catalog_query, @@ -1014,23 +1021,27 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, " AND NOT p.inherited\n" " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n" " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" - " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" - " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" - " AND s.stainherit))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats s\n" + " WHERE s.schemaname OPERATOR(pg_catalog.=) ns.nspname\n" + " AND s.tablename OPERATOR(pg_catalog.=) c.relname\n" + " AND s.attname OPERATOR(pg_catalog.=) a.attname\n" + " AND s.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* inheritance and extended stats */ appendPQExpBufferStr(&catalog_query, " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n" + " JOIN pg_catalog.pg_namespace en" + " ON en.oid OPERATOR(pg_catalog.=) e.stxnamespace\n" " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n" " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" " AND c.relhassubclass\n" " AND NOT p.inherited\n" " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n" " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n" - " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n" - " AND d.stxdinherit))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats_ext d\n" + " WHERE d.statistics_schemaname OPERATOR(pg_catalog.=) en.nspname\n" + " AND d.statistics_name OPERATOR(pg_catalog.=) e.stxname\n" + " AND d.inherited))\n"); appendPQExpBufferStr(&catalog_query, " )\n"); } base-commit: 12da45742cfd15d9fab151b25400d96a1febcbde -- 2.50.1
