Subject: Re: list of extended statistics on psql
To: "Shinoda, Noriyoshi (PN Japan FSIP)" <noriyoshi.shinoda@hpe.com>,
 Julien Rouhaud <rjuju123@gmail.com>,
 Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 Michael Paquier <michael@paquier.xyz>,
 Pavel Stehule <pavel.stehule@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <20210107144713.GA8872@alvherre.pgsql>
 <61cba5af-5a58-6bda-610c-420077b780c3@enterprisedb.com>
 <1045d2cb-7ecd-e6bf-cb50-8eeea89cc4b0@nttcom.co.jp_1>
 <9e76ce7a-e7ab-5f08-9938-59fffc20d106@enterprisedb.com>
 <29e16ceb-8777-4297-19c8-245242dd7473@enterprisedb.com>
 <876efa7e-2067-a4f9-d7e8-f6025440c870@nttcom.co.jp_1>
 <0e84aa72-25f2-1849-753f-f827949132b3@enterprisedb.com>
 <85e7e1e7-c2f0-a4a0-61de-6e7ecaee44e3@nttcom.co.jp_1>
 <79d3a4c5-1efe-5b9e-c06e-3a907b531013@nttcom.co.jp_1>
 <20210115084741.GA91293@nol>
 <2eb51458-7475-17d3-cb13-b3c950b2f930@enterprisedb.com>
 <5e0fb573-3c38-ee39-885c-bdc01f116335@enterprisedb.com>
 <TU4PR8401MB115239DC3FE567BE637F7544EEA50@TU4PR8401MB1152.NAMPRD84.PROD.OUTLOOK.COM>
 <d876a1e3-9a6c-712e-7ab8-3dfa8a4cac1e@enterprisedb.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <a9c2eb3f-a4e7-b196-9a87-f2acfb5c4859@enterprisedb.com>
Date: Sun, 17 Jan 2021 15:31:57 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.0
MIME-Version: 1.0
In-Reply-To: <d876a1e3-9a6c-712e-7ab8-3dfa8a4cac1e@enterprisedb.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


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?
>>
> 
> 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.

regards

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