From: | Reece Hart <reece(at)harts(dot)net> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Cc: | Glen Parker <glenebob(at)nwlink(dot)com> |
Subject: | Re: Vacuum non-clustered tables only |
Date: | 2007-05-17 21:43:23 |
Message-ID: | 1179438203.4782.58.camel@snafu.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2007-05-16 at 15:40 -0700, Glen Parker wrote:
> Is there a semi-easy way vacuum all tables in a database *except*
> those that are clustered?
Yes, it can be done "semi-easily". Here's an example:
select N.nspname,relname
from pg_class C
join pg_namespace N on C.relnamespace=N.oid
where relkind='r' and not exists
(select * from pg_index I
where C.oid=I.indrelid and I.indisclustered);
Many of us build statements in SQL itself. For instance, replace the
first line above with
select 'vacuum '||N.nspname||'.'||C.relname||';' as vacuum_cmd
and now the query returns executable statements. You probably want to
restrict by namespace/schema name as well. If you put all of that into
a view, it's then easy to do something like
$ psql -c 'select vacuum_cmd from view' | psql -aX
which uses one connection to select the script, and another to execute
it.
Good luck,
Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
From | Date | Subject | |
---|---|---|---|
Next Message | Glen Parker | 2007-05-17 21:46:57 | Re: Vacuum non-clustered tables only |
Previous Message | PFC | 2007-05-17 21:40:02 | Re: Fault Tolerant Postgresql (two machines, two postmasters, one disk array) |