pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb for schema only
5+ messages / 3 participants
[nested] [flat]
* vacuumdb for schema only
@ 2010-09-07 22:39 Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Isabella Ghiurea @ 2010-09-07 22:39 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb for schema only
@ 2010-09-08 00:17 Fabrízio de Royes Mello <fabriziomello@gmail.com>
parent: Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Fabrízio de Royes Mello @ 2010-09-08 00:17 UTC (permalink / raw)
To: isabella.ghiurea@nrc-cnrc.gc.ca; +Cc: pgsql-admin
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.
Best regards,
--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb for schema only
@ 2010-09-08 05:34 Bèrto ëd Sèra <berto.d.sera@gmail.com>
parent: Fabrízio de Royes Mello <fabriziomello@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bèrto ëd Sèra @ 2010-09-08 05:34 UTC (permalink / raw)
To: fabriziomello@gmail.com; +Cc: isabella.ghiurea@nrc-cnrc.gc.ca; pgsql-admin
Hi!
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 bdteste
>
> The 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb for schema only
@ 2010-09-08 13:06 Fabrízio de Royes Mello <fabriziomello@gmail.com>
parent: Bèrto ëd Sèra <berto.d.sera@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Fabrízio de Royes Mello @ 2010-09-08 13:06 UTC (permalink / raw)
To: Bèrto ëd Sèra <berto.d.sera@gmail.com>; +Cc: isabella.ghiurea@nrc-cnrc.gc.ca; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: vacuumdb for schema only
@ 2010-09-08 13:10 Bèrto ëd Sèra <berto.d.sera@gmail.com>
parent: Fabrízio de Royes Mello <fabriziomello@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bèrto ëd Sèra @ 2010-09-08 13:10 UTC (permalink / raw)
To: fabriziomello@gmail.com; +Cc: isabella.ghiurea@nrc-cnrc.gc.ca; pgsql-admin
Nice to know that, which means we can only send out scripts by cron...
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 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
>
>
--
==============================
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-09-08 13:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-07 22:39 vacuumdb for schema only Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>
2010-09-08 00:17 ` Re: vacuumdb for schema only Fabrízio de Royes Mello <fabriziomello@gmail.com>
2010-09-08 05:34 ` Re: vacuumdb for schema only Bèrto ëd Sèra <berto.d.sera@gmail.com>
2010-09-08 13:06 ` Re: vacuumdb for schema only Fabrízio de Royes Mello <fabriziomello@gmail.com>
2010-09-08 13:10 ` Re: vacuumdb for schema only Bèrto ëd Sèra <berto.d.sera@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox