Re: Truncate table at a certain size. - Mailing list pgsql-sql

On Mon, Apr 28, 2008 at 7:24 PM, Gurjeet Singh <[email protected]> wrote:
> On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik
> <[email protected]> wrote:
>
> > Hello,
> >
> > What would be the best method to truncate a table once it reaches a
> > certain size.
> >
> > For instance, a table named log.  I can check the size of the log;
> >
> >   db=# select pg_relation_size('log');
> >   pg_relation_size
> >   ------------------
> >                 8192
> >   (1 row)
> >
> >
> > What I would like to do is
> >
> > If table log, is greater than xxxx bytes
> >   then truncate table log
> >
>
> No, you wouldn't want to do this.... First and foremost, SQL (and Postgres)
> does not guarantee that the new rows you are inserting land at some specific
> point in storage; so you can't really be sure which part you want to
> truncate.

I think the OP was talking about running the truncate command on them...

if select pg_relation_size('log') > somesize then truncate log;

If that's the case he can either iterate a list of tables in plpgsql,
an external scripting language, or write some select statement that
creates truncates for all the tables over x size.

something like:

select 'tuncate '||relname||';' from (rest of query from psql -E and
\d here) where pg_relation(relname) > somesize;