Re: list of extended statistics on psql

On 1/18/21 8:31 AM, Tatsuro Yamada wrote:
> Hi Tomas and Shinoda-san,
>
> On 2021/01/17 23:31, Tomas Vondra wrote:
>>
>>
>> On 1/17/21 3:01 AM, Tomas Vondra wrote:
>>> On 1/17/21 2:41 AM, Shinoda, Noriyoshi (PN Japan FSIP) wrote:
>>>> Hi, hackers.
>>>>
>>>> I tested this committed feature.
>>>> It doesn't seem to be available to non-superusers due to the
>>>> inability to access pg_statistics_ext_data.
>>>> Is this the expected behavior?
>
>
> Ugh. I overlooked the test to check the case of the user hasn't
> Superuser privilege. The user without the privilege was able to access
> pg_statistics_ext. Therefore I supposed that it's also able to access
> pg_statics_ext_data. Oops.
>
>
>>> Hmmm, that's a good point. Bummer we haven't noticed that earlier.
>>>
>>> I wonder what the right fix should be - presumably we could do
>>> something like pg_stats_ext (we can't use that view directly, because
>>> it formats the data, so the sizes are different).
>>>
>>> But should it list just the stats the user has access to, or should
>>> it list everything and leave the inaccessible fields NULL?
>>>
>>
>> I've reverted the commit - once we find the right way to handle this,
>> I'll get it committed again.
>>
>> As for how to deal with this, I can think of about three ways:
>>
>> 1) simplify the command to only print information from
>> pg_statistic_ext (so on information about which stats are built or sizes)
>>
>> 2) extend pg_stats_ext with necessary information (e.g. sizes)
>>
>> 3) create a new system view, with necessary information (so that
>> pg_stats_ext does not need to be modified)
>>
>> 4) add functions returning the necessary information, possibly only
>> for statistics the user can access (similarly to what pg_stats_ext does)
>>
>> Options 2-4 have the obvious disadvantage that this won't work on
>> older releases (we can't add views or functions there). So I'm leaning
>> towards #1 even if that means we have to remove some of the details.
>> We can consider adding that for new releases, though.
>
>
> Thanks for the useful advice. I go with option 1).
> The following query is created by using pg_stats_ext instead of
> pg_statistic_ext and pg_statistic_ext_data. However, I was confused
> about writing a part of the query for calculating MCV size because
> there are four columns related to MCV. For example, most_common_vals,
> most_common_val_nulls, most_common_freqs, and most_common_base_freqs.
> Currently, I don't know how to calculate the size of MCV by using the
> four columns. Thoughts? :-)

Well, my suggestion was to use pg_statistic_ext, because that lists all
statistics, while pg_stats_ext is filtering statistics depending on
access privileges. I think that's more appropriate for \dX, the contents
should not change depending on the user.

Also, let me clarify - with option (1) we'd not show the sizes at all.
The size of the formatted statistics may be very different from the
on-disk representation, so I see no point in showing it in \dX.

We might show other stats (e.g. number of MCV items, or the fraction of
data represented by the MCV list), but the user can inspect pg_stats_ext
if needed.

What we might do is to show those stats when a superuser is running this
command, but I'm not sure that's a good idea (or how difficult would it
be to implement).

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company