Re: Vacuum non-clustered tables only

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