vacuum full for all databases

[Ilia Chipitsine]

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_d atabase WHERE NOT
datistemplate LOOP
raise notice ''datname = %'',list.datnam e;

END LOOP;

RETURN 0;

END;

'
LANGUAGE plpgsql;
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Peter Eisentraut]

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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

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.datnam e;

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

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Ilia Chipitsine]

> 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.datnam e;

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Oleg Bartunov]

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.datnam e;

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: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: [phone]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings