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.
