On 02/29/2016 08:13 AM, David G. Johnston wrote:
On Mon, Feb 29, 2016 at 8:28 AM, Stephen Frost <sfrost@xxxxxxxxxxx
<mailto:sfrost@xxxxxxxxxxx>>wrote:
* David G. Johnston (david.g.johnston@xxxxxxxxx
<mailto:david.g.johnston@xxxxxxxxx>) wrote:
> On Mon, Feb 29, 2016 at 6:31 AM, Stephen Frost <sfrost@xxxxxxxxxxx <mailto:sfrost@xxxxxxxxxxx>> wrote:
>
> > * David G. Johnston (david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>) wrote:
> > > Given the amount of damage a person with write access to a table can get
> > > into it seems pointless to not allow them to analyze the table after
> > their
> > > updates - since best practices would say that normal work with a table
> > > should not be performed by an owner.
> > >
> > > I should the check for whether a given user can or cannot analyze a table
> > > should be whether the user has INSERT, UPDATE, or DELETE privileges.
> >
> > Realistically, ANALYZE is a background/maintenance task that autovacuum
> > should be handling for you.
>
> Then my recent experience of adding a bunch of records and having the
> subsequent select query take forever because the table wasn't analyzed is
> not supposed to happen? What am I doing wrong then that autovacuum didn't
> run for me?
Perhaps nothing. Making autovacuum more aggressive is a trade-off and
evidently there weren't enough changes or perhaps not enough time for
autovacuum to realize it needed to kick in and re-analyze the table.
One thought about how to address that might be to have a given backend,
which is already sending stats info to the statistic collector, somehow
also bump autovacuum to wake it up from its sleep to go analyze the
tables just modified. This is all very hand-wavy as I don't have time
at the moment to run it down, but I do think it'd be good to reduce the
need to run ANALYZE by hand after every data load.
Improving it is desirable but it wouldn't preclude this desire.
> > > I suppose row-level-security might come into play here...
> >
> > Yes, you may only have access to a subset of the table.
> >
> >
> TBH, since you cannot see the data being analyzed I don't see a security
> implication here if you allow someone to ANALYZE the whole table even when
> RLS is in place.
I wasn't looking at it from a security implication standpoint as I
suspect that any issue there could actually be addressed, if any exist.
What I was getting at is that you're making an assumption that any user
with DML rights on the table also has enough information about the table
overall to know when it makes sense to ANALYZE the table or not. That's
a bit of a stretch to begin with, but when you consider that RLS may be
involved and the user may only have access to 1% (or less) of the
overall table, it's that much more of a reach.
So the typical user doesn't know or even care that what they just did
needs to be analyzed. The situation is no worse than it is today. But
as someone who writes many scripts and applications to perform bulk
writing and data analysis I'd like those scripts to use restricted
authorization credentials while still being able to run ANALYZE between
performing the bulk DML and the running the SELECT statements needed to
get the newly generated data out of the database.
Maybe?:
CREATE OR REPLACE FUNCTION public.analyze_test(tbl_name character varying)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
EXECUTE 'ANALYZE ' || quote_ident(tbl_name);
END;
$function$
> If we had plenty more bits to allow ANALYZE to be independently
> > GRANT'able, then maybe, but those are a limited resource.
> >
>
> The planner and system performance seems important enough to give it such
> a resource. But as I stated initially I personally believe that a user
> with INSERT/DELETE/UPDATE permissions on a table (maybe require all three)
> should also be allowed to ANALYZE said table.
I don't think requiring all three would make any sense and would,
instead, simply be confusing. I'm not completely against your general
idea, but let's keep it simple.
Agreed.
David J.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
