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.comMail sent from my BlackLaptop device
