Skip site navigation (1) Skip section navigation (2)

Re: Truncate table at a certain size.

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
Cc: "Dana Huggard - Navarik" <dhuggard(at)navarik(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Truncate table at a certain size.
Date: 2008-04-29 03:31:17
Message-ID: dcc563d10804282031m45648f1ar91e6adb10f2343@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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;

In response to

pgsql-sql by date

Next:From: Bart DegryseDate: 2008-04-29 07:20:35
Subject: Field Alias Alternative
Previous:From: Scott MarloweDate: 2008-04-29 02:22:05
Subject: Re: currval of sequence "my_seq" is not yet defined in this session

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group