Обсуждение: vacuumdb for schema only
Hi List, I would like to know if there is an option to run full vacuumdb for a specific schema only, I see there is option for tables or whole db . Thank you Isabella -- ----------------------------------------------------------- Isabella A. Ghiurea Isabella.Ghiurea@nrc-cnrc.gc.ca Canadian Astronomy Data Centre |http://www.nrc-cnrc.gc.ca/eng/services/hia/data-centre.html National Research Council of Canada, Herzberg Institute of Astrophysics 5071 West Saanich Road, Victoria BC V9E 2E7, Canada Phone: 250 363-3446 fax: 250 363-0045
2010/9/7 Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>
Hi List,
I would like to know if there is an option to run full vacuumdb for a specific schema only, I see there is option for tables or whole db .
No, but you can do like this using "psql" :
psql -U postgres -t -A -c "select 'VACUUM '||table_schema||'.'||table_name||';' from information_schema.tables where table_schema = 'public'" bdteste | psql -U postgres bdteste
The example above will run VACUUM to all tables in schema called 'public' in 'bdteste' database.
--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Hi!
--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.
I would also expect you to be able to make a Stored Procedure executing the same command, although I never tried it myself.
Bèrto
On 8 September 2010 03:17, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:
2010/9/7 Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>Hi List,
I would like to know if there is an option to run full vacuumdb for a specific schema only, I see there is option for tables or whole db .No, but you can do like this using "psql" :psql -U postgres -t -A -c "select 'VACUUM '||table_schema||'.'||table_name||';' from information_schema.tables where table_schema = 'public'" bdteste | psql -U postgres bdtesteThe example above will run VACUUM to all tables in schema called 'public' in 'bdteste' database.Best regards,
--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.
2010/9/8 Bèrto ëd Sèra <berto.d.sera@gmail.com>
Hi!I would also expect you to be able to make a Stored Procedure executing the same command, although I never tried it myself.
It is not possible... vacuum cannot be executed inside a function or transaction.
See de sample:
-- Using function
CREATE OR REPLACE FUNCTION fc_vacuum(TEXT) RETURNS VOID AS
$$
BEGIN
EXECUTE 'VACUUM '||$1;
RETURN;
END;
$$
LANGUAGE plpgsql;
postgres@bdteste=# select fc_vacuum('foo');
ERROR: VACUUM cannot be executed from a function
CONTEXT: SQL statement "VACUUM foo"
PL/pgSQL function "fc_vacuum" line 2 at execute statement
-- Using transaction
postgres@bdteste=# begin;
BEGIN
postgres@bdteste=# VACUUM foo;
ERROR: VACUUM cannot run inside a transaction block
Best regards,
--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Nice to know that, which means we can only send out scripts by cron...
--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.
On 8 September 2010 16:06, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:
2010/9/8 Bèrto ëd Sèra <berto.d.sera@gmail.com>Hi!I would also expect you to be able to make a Stored Procedure executing the same command, although I never tried it myself.It is not possible... vacuum cannot be executed inside a function or transaction.See de sample:-- Using functionCREATE OR REPLACE FUNCTION fc_vacuum(TEXT) RETURNS VOID AS$$BEGINEXECUTE 'VACUUM '||$1;RETURN;END;$$LANGUAGE plpgsql;postgres@bdteste=# select fc_vacuum('foo');ERROR: VACUUM cannot be executed from a functionCONTEXT: SQL statement "VACUUM foo"PL/pgSQL function "fc_vacuum" line 2 at execute statement-- Using transactionpostgres@bdteste=# begin;BEGINpostgres@bdteste=# VACUUM foo;ERROR: VACUUM cannot run inside a transaction blockBest regards,
--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.