Обсуждение: vacuum full for all databases

Dear Sirs

I'm about to write plpgsql function which will "vacuum full" all existing
databases. Below is an example how to get list of databases.

What should I write instead of "raise notice" ?


CREATE OR REPLACE FUNCTION vacuum_all() RETURNS integer
    AS '
DECLARE
  query text;
  list RECORD;
BEGIN

FOR list IN SELECT datname FROM pg_catalog.pg_database WHERE NOT
datistemplate LOOP
  raise notice ''datname = %'',list.datname;

END LOOP;

RETURN 0;

END;



'
    LANGUAGE plpgsql;

Am Donnerstag, 2. September 2004 09:32 schrieb Ilia Chipitsine:
> I'm about to write plpgsql function which will "vacuum full" all existing
> databases. Below is an example how to get list of databases.
>
> What should I write instead of "raise notice" ?

You normally cannot access different databases from one database session.  But
you could use dblink from contrib to overcome this restriction.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Ilia Chipitsine wrote:
> Dear Sirs
>
> I'm about to write plpgsql function which will "vacuum full" all existing
> databases. Below is an example how to get list of databases.
>
> What should I write instead of "raise notice" ?

>   raise notice ''datname = %'',list.datname;

Something like:
   EXECUTE ''VACUUM FULL '' || list.datname;

--
   Richard Huxton
   Archonet Ltd

> Ilia Chipitsine wrote:
> > Dear Sirs
> >
> > I'm about to write plpgsql function which will "vacuum full" all existing
> > databases. Below is an example how to get list of databases.
> >
> > What should I write instead of "raise notice" ?
>
> >   raise notice ''datname = %'',list.datname;
>
> Something like:
>    EXECUTE ''VACUUM FULL '' || list.datname;

"vacuum full" accepts relation name, not database name

$ psql -U pgsql template1
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# vacuum full site_b170;
ERROR:  relation "site_b170" does not exist
template1=#

>
> --
>    Richard Huxton
>    Archonet Ltd
>

On Thu, 2 Sep 2004, Richard Huxton wrote:

> Ilia Chipitsine wrote:
> > Dear Sirs
> >
> > I'm about to write plpgsql function which will "vacuum full" all existing
> > databases. Below is an example how to get list of databases.
> >
> > What should I write instead of "raise notice" ?
>
> >   raise notice ''datname = %'',list.datname;
>
> Something like:
>    EXECUTE ''VACUUM FULL '' || list.datname;
>
>

vacuumdb --all



    Regards,
        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83