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

Re: Truncate table at a certain size.

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: "Dana Huggard - Navarik" <dhuggard(at)navarik(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Truncate table at a certain size.
Date: 2008-04-29 01:24:22
Message-ID: (view raw or whole thread)
Lists: pgsql-sql
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

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

Best regards,
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com


Mail sent from my BlackLaptop device

In response to


pgsql-sql by date

Next:From: Scott MarloweDate: 2008-04-29 02:22:05
Subject: Re: currval of sequence "my_seq" is not yet defined in this session
Previous:From: Tom LaneDate: 2008-04-28 23:14:58
Subject: Re: information_schema.referential_constraints permissions

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