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: 65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com (view raw or flat)
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
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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

In response to

Responses

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-2014 The PostgreSQL Global Development Group