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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2008-04-29 02:22:05 | Re: currval of sequence "my_seq" is not yet defined in this session |
Previous Message | Tom Lane | 2008-04-28 23:14:58 | Re: information_schema.referential_constraints permissions |