Thread: what's difference between vacuum analyze and analyze?
I was asked to write a security definer function to make other user can issue vacuum and analyze command? Friends in this community said vacuum doesn't work inside the function or multi command. How about analyze. I know vacuum is something much like oracle shrink which is used to reorganize the space in the disk. Analyze is used to collect the statistic info of the table. Does analyze works in Function or mulit-commands? Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/what-s-difference-between-vacuum-analyze-and-analyze-tp5600887p5600887.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Mar 29, 2012 at 2:24 AM, leaf_yxj <leaf_yxj@163.com> wrote: > I was asked to write a security definer function to make other user can issue > vacuum and analyze command? Friends in this community said vacuum doesn't > work inside the function or multi command. How about analyze. I know vacuum > is something much like oracle shrink which is used to reorganize the space > in the disk. Analyze is used to collect the statistic info of the table. > Does analyze works in Function or mulit-commands? There's an easy way to find out. Try it, and see if you get an error back! http://www.catb.org/~esr/faqs/smart-questions.html You may find that it isn't possible. If so, you may want to consider a non-SQL solution; for instance, a shell script that invokes vacuum/analyze. Chris Angelico
On 03/28/2012 08:24 AM, leaf_yxj wrote: > I was asked to write a security definer function to make other user can issue > vacuum and analyze command? Friends in this community said vacuum doesn't > work inside the function or multi command. How about analyze. I know vacuum > is something much like oracle shrink which is used to reorganize the space > in the disk. Analyze is used to collect the statistic info of the table. > Does analyze works in Function or mulit-commands? test=> BEGIN ; BEGIN test=> ANALYZE ; WARNING: skipping "pg_authid" --- only superuser can analyze it WARNING: skipping "pg_database" --- only superuser can analyze it WARNING: skipping "pg_db_role_setting" --- only superuser can analyze it WARNING: skipping "pg_tablespace" --- only superuser can analyze it WARNING: skipping "pg_pltemplate" --- only superuser can analyze it WARNING: skipping "pg_auth_members" --- only superuser can analyze it WARNING: skipping "pg_shdepend" --- only superuser can analyze it WARNING: skipping "pg_shdescription" --- only superuser can analyze it ANALYZE test=> COMMIT ; COMMIT test=> BEGIN ; BEGIN test=> VACUUM ANALYZE ; ERROR: VACUUM cannot run inside a transaction block test=> For details: http://www.postgresql.org/docs/9.1/interactive/sql-vacuum.html http://www.postgresql.org/docs/9.1/interactive/sql-analyze.html > > > Thanks. > > Regards. > > > Grace > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/what-s-difference-between-vacuum-analyze-and-analyze-tp5600887p5600887.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > -- Adrian Klaver adrian.klaver@gmail.com