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

David,

* David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> On Thu, Mar 24, 2016 at 4:51 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > I don't see any reason why the patch itself would be terribly difficult,
> > but are we sure we'd want just ANALYZE and not VACUUM also? Which would
> > have to be another bit, since those are pretty different actions.
>
> In the limited experience that​ prompted this requested the benefit of
> performing a VACUUM is significantly less than the benefit of performing
> ANALYZE, and the cost of the former is considerably higher. I'm quite
> content to leave VACUUM decisions to the auto-vacuum process which balances
> the benefit of removing bloat with the I/O cost of doing so.

I guess I don't entirely follow that logic. autovacuum, even though
it's name doesn't imply it, is *also* quite responsible for ensuring
that ANALYZE is done regularly on the tables and even has options to
control when ANALYZE is run which would it to run more frequently than
vacuums.

Further, a lot of ETL could have very good reason to want to run a
VACUUM, especially with the changes that we continue to make which make
that process less and less expensive of an operation to run.

> > The question really is- what other things might we want as grantable
> > rights in the future? Once these 16 bits are gone, it's a whole bunch
> > of work to get more.
>
> If I am reading parsenodes.h correctly we presently use only 12 of 16 bits
> and those that are present all seem ancient. With no other existing need
> to add a single additional grantable option, let alone 4, I'm not see this
> as being particularly concerning.

They're not all ancient- TRUNCATE was added not that long ago and took
quite a few years of convincing before it was accepted (I asked for it
when I first started working on PG, some 15-or-so years ago and it
wasn't actually included until 3 or 4 years ago, iirc).

Further, as we add new features, new kinds of GRANTs can be needed.
Consider the case of auditing, for example. When we finally get around
to adding support for proper in-core auditing, it may be desirable for
individuals other than the owner of a relation to be able to control the
auditing of the table.

> Let someone else argue for inclusion of VACUUM before considering adding it
> - all I believe that we need is ANALYZE. I want programs doing ETL to be
> able to get the system into "good-enough" shape to be functional;
> maintenance processes can deal with the rest.

ANALYZE is a maintenance process too, really, so I don't entirely buy
your argument here. Either we support having these maintanence-type
actions being performed by non-owners, or we don't and encourage
everyone to configure autovacuum to meet their needs.

Thanks!

Stephen