Postgres Pro
Downloads
Home   >   mailing lists

Thread: Truncate table at a certain size.

Truncate table at a certain size.

From
Dana Huggard - Navarik
Date:
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


Maybe I'll dump it to a flat file first, (yes, it should really be a
flat file to start with, and not in a db.)

Thank you.

Re: Truncate table at a certain size.

From
"Gurjeet Singh"
Date:
On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik <dhuggard@navarik.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.

But by design, in an ever growing table, Postgres puts new rows at the tail end; so even with this knowledge you don't want to truncate the table, because your older rows are towards the head/beginning of th table and not at the tail.

Maybe I'll dump it to a flat file first, (yes, it should really be a
flat file to start with, and not in a db.)


Yes, that'd be a better approach; use two files of fixed size, and cycle between them (much like how Postgres deals with it's transaction logs (in pg_xlog/)).

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: Truncate table at a certain size.

From
"Scott Marlowe"
Date:
On Mon, Apr 28, 2008 at 7:24 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik
> <dhuggard@navarik.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;