From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
Cc: | "Dana Huggard - Navarik" <dhuggard(at)navarik(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Truncate table at a certain size. |
Date: | 2008-04-29 03:31:17 |
Message-ID: | dcc563d10804282031m45648f1ar91e6adb10f2343@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Mon, Apr 28, 2008 at 7:24 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> 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.
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Bart Degryse | 2008-04-29 07:20:35 | Field Alias Alternative |
Previous Message | Scott Marlowe | 2008-04-29 02:22:05 | Re: currval of sequence "my_seq" is not yet defined in this session |