Re: Only owners can ANALYZE tables...seems overly restrictive

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, "John R Pierce *EXTERN*" <pierce(at)hogranch(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Only owners can ANALYZE tables...seems overly restrictive
Date: 2016-02-29 17:39:24
Message-ID: 16134.1456767564@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Mon, Feb 29, 2016 at 9:27 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> wrote:
>> See http://www.postgresql.org/docs/current/static/planner-stats.html
>> "The amount of information stored in pg_statistic by ANALYZE, in
>> particular the
>> maximum number of entries in the most_common_vals and histogram_bounds
>> arrays
>> for each column, can be set on a column-by-column basis using the
>> ALTER TABLE SET STATISTICS command, or globally by setting the
>> default_statistics_target configuration variable."

> Being able to run ANALYZE on a table in no way implies that I should be
> allowed to run ALTER TABLE SET STATISTICS on the same.

You're missing the point. If the table owner has *not* run ALTER TABLE
SET STATISTICS, which surely is the typical situation, then whoever runs
ANALYZE can control the volume of stats generated by setting
default_statistics_target locally in his session. Thus, if we allow
non-owners to run ANALYZE, they'd be able to mess things up by setting
the stats target either much lower or much higher than the table owner
expected. ("Much higher" would be bad in a different way than "much
lower", but still bad.)

I imagine this could be addressed by some rule about how if you don't
own the table then your default_statistics_target is overridden by
the global setting, but that would be a mess both conceptually and
implementation-wise.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rémi Cura 2016-02-29 17:56:44 bloated postgres data folder, clean up
Previous Message Adrian Klaver 2016-02-29 17:36:41 Re: Only owners can ANALYZE tables...seems overly restrictive