From: Gurjeet Singh <singh.gurjeet@gmail.com>
To: Dana Huggard - Navarik <dhuggard@navarik.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Truncate table at a certain size.
Date: Tue, 29 Apr 2008 06:54:22 +0530
Message-ID: <65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com> (raw)
In-Reply-To: <1209422104.12712.12.camel@H-S12.internal.navarik.com>
References: <1209422104.12712.12.camel@H-S12.internal.navarik.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: singh.gurjeet@gmail.com, dhuggard@navarik.com
Subject: Re: Truncate table at a certain size.
In-Reply-To: <65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox