Обсуждение: Truncate table at a certain size.
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
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.)
Thank you.
On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik <dhuggard@navarik.com> wrote:
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.
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
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
On Mon, Apr 28, 2008 at 7:24 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> 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.
I think the OP was talking about running the truncate command on them...
if select pg_relation_size('log') > somesize then truncate log;
If that's the case he can either iterate a list of tables in plpgsql,
an external scripting language, or write some select statement that
creates truncates for all the tables over x size.
something like:
select 'tuncate '||relname||';' from (rest of query from psql -E and
\d here) where pg_relation(relname) > somesize;