Re: Optimize MCV stats for sortable types and utilize sorted-order properties

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) 
44973863(at)qq(dot)com