Re: Vacuum non-clustered tables only - Mailing list pgsql-general

From Reece Hart
Subject Re: Vacuum non-clustered tables only
Date
Msg-id 1179438203.4782.58.camel@snafu.site
Whole thread Raw
In response to Vacuum non-clustered tables only  (Glen Parker <glenebob@nwlink.com>)
Responses Re: Vacuum non-clustered tables only
List 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


pgsql-general by date:

Previous
From: "Michael Nolan"
Date:
Subject: Re: Large Database Restore
Next
From: Glen Parker
Date:
Subject: Re: Vacuum non-clustered tables only