Hi hackers, While working on dump/restore and postgres_fdw support for this patch [1], I'd like to get some feedback on how to expose the MCV representation through pg_stats and pg_stats_ext_exprs.
The patch introduces a new statistics kind (kind 8) for MCVs sorted by value, while retaining the existing frequency-ordered representation (kind 1). During restore, we need to know which representation the exported arrays use. Restoring value-ordered arrays as kind 1 could cause subsequent consumers to misinterpret their ordering. Initially, I avoided adding columns to these views to preserve their existing result shapes. Instead, my revised pg_dump and postgres_fdw queries join pg_stats to the underlying pg_statistic catalog to obtain the statistics kind. However, testing exposed a permission regression. A non-superuser with SELECT privilege on a table can read its corresponding rows from pg_stats, but the additional reference to pg_statistic fails with: ERROR: permission denied for table pg_statistic For the same role and table, the baseline pg_dump --statistics-only succeeds, while the revised query fails. Statistics import through postgres_fdw also fails when using a non-superuser remote mapping, although querying the foreign table's data succeeds. Using a remote superuser allows the statistics import to succeed. I found two relevant precedents for extending the statistics views: - Commit [bc3c8db8ae2f](https://github.com/postgres/postgres/commit/bc3c8db8ae2f899cfd65edce99fe9cf65caf1ed7) added range_length_histogram, range_empty_frac, and range_bounds_histogram to the end of pg_stats in PostgreSQL 17, along with a catalog version bump and updates to the documentation and regression test output. - Commit [307447e6dbc0](https://github.com/postgres/postgres/commit/307447e6dbc035c0e6e7a8ce9cee704a3b710ee9) added the same fields to pg_stats_ext_exprs in PostgreSQL 19, also with a catalog version bump. Current pg_dump code handles the range-statistics fields by checking the source server version and selecting NULL placeholders for servers predating PostgreSQL 17. I think a similar version-compatibility approach could work for the new MCV metadata. My current proposal is to: - Append a smallint column named most_common_vals_kind to both views. It would return the representation kind (1 or 8) corresponding to the most_common_vals and most_common_freqs actually exposed, and NULL when no MCV slot is present. - Preserve the existing privilege checks and row-security filtering of both views, and have pg_dump and postgres_fdw obtain the kind through the views rather than accessing pg_statistic directly. - For source servers predating this addition, assume kind 1 for their existing single-variable MCV representation. For expression statistics, include the kind in each exported JSON object. - Update the catalog version, documentation, and relevant regression tests. Appending a column would preserve the positions of existing columns, although clients that assume a fixed number of columns from SELECT * could still require adjustments. Also, exposing the representation kind would not by itself address tools that assume MCV arrays are always frequency-ordered. The range-statistics precedents support extending these views, but do not directly resolve the compatibility implications of changing the MCV ordering. I'd appreciate feedback on two related but distinct questions: 1. Would adding most_common_vals_kind to both pg_stats and pg_stats_ext_exprs be an appropriate way to address the statistics-export permission issue? 2. Should the public statistics views continue to guarantee frequency-ordered MCVs, with value-ordered statistics exposed through a separate interface? Or would explicitly exposing the representation kind be sufficient to support the new ordering? I'd welcome suggestions, particularly on system view design, backward compatibility, and the behavior of dump/restore and postgres_fdw. Thanks! [1] https://commitfest.postgresql.org/patch/7302/ Attached test SQL and results: ====================for super role, ok SELECT current_user, rolsuper FROM pg_roles WHERE rolname = current_user; current_user | rolsuper --------------+---------- xman | t (1 row) xman7=# SELECT s.attname, sat.stakind1 FROM pg_catalog.pg_stats AS s LEFT JOIN pg_catalog.pg_statistic AS sat ON sat.starelid = 't_sort_mcv_100'::regclass AND sat.staattnum = 1 AND sat.stainherit = s.inherited WHERE s.schemaname = 'public' AND s.tablename = 't_sort_mcv_100' and sat.stakind1 in (1, 8) limit 10; attname | stakind1 ---------+---------- name | 8 (1 row) xman7=# ====================for non super role, error CREATE ROLE mcv_reader_1 NOLOGIN; SET ROLE mcv_reader_1; SELECT current_user, rolsuper FROM pg_roles WHERE rolname = current_user; SELECT s.attname, sat.stakind1 FROM pg_catalog.pg_stats AS s LEFT JOIN pg_catalog.pg_statistic AS sat ON sat.starelid = 't_sort_mcv_100'::regclass AND sat.staattnum = 1 AND sat.stainherit = s.inherited WHERE s.schemaname = 'public' AND s.tablename = 't_sort_mcv_100' and sat.stakind1 in (1, 8) limit 10; xman7=# CREATE ROLE mcv_reader_1 NOLOGIN; CREATE ROLE xman7=# SET ROLE mcv_reader_1; SET xman7=> SELECT current_user, rolsuper FROM pg_roles WHERE rolname = current_user; current_user | rolsuper --------------+---------- mcv_reader_1 | f (1 row) xman7=> SELECT s.attname, sat.stakind1 FROM pg_catalog.pg_stats AS s LEFT JOIN pg_catalog.pg_statistic AS sat ON sat.starelid = 't_sort_mcv_100'::regclass AND sat.staattnum = 1 AND sat.stainherit = s.inherited WHERE s.schemaname = 'public' AND s.tablename = 't_sort_mcv_100' and sat.stakind1 in (1, 8) limit 10; ERROR: permission denied for table pg_statistic xman7=> regards, -- ZizhuanLiu (X-MAN) [email protected]
