On Mon, Feb 29, 2016 at 10:36 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> 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$ > > Yes, a security definer function - and setting execute permissions appropriately - would work. But it is a hack to work around a restriction that, in theory, need not exist. I understand that our implementation - namely the presence of a publically visible GUC and uninhibitied SET usage - may make reality more complicated than this. I guess I don't see why anyone other than the database owner and superuser (if that, it probably could be made to be fixed at startup) should be allowed to SET default_statistics_target. If a table owner wants to play with different levels they can always just ALTER TABLE SET - which has the benefit (though maybe this is undesirable in some instances...) of making the alteration effective during auto-vacuum runs. ANALYZE is something that needs to happen frequently and commonly on a running system for proper operation. In comparison the statistic targets are basically frozen values aside from periods of experimentation. We have our priorities backwards if SET is preventing more user-friendly usage of ANALYZE. David J.
