Re: Truncate table at a certain size.

On Mon, Apr 28, 2008 at 7:24 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik
> <dhuggard(at)navarik(dot)com> 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;