From: Scott Marlowe <scott.marlowe@gmail.com>
To: Gurjeet Singh <singh.gurjeet@gmail.com>
Cc: Dana Huggard - Navarik <dhuggard@navarik.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Truncate table at a certain size.
Date: Mon, 28 Apr 2008 21:31:17 -0600
Message-ID: <dcc563d10804282031m45648f1ar91e6adb10f2343@mail.gmail.com> (raw)
In-Reply-To: <65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com>
References: <1209422104.12712.12.camel@H-S12.internal.navarik.com>
<65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com>
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;
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: scott.marlowe@gmail.com, singh.gurjeet@gmail.com, dhuggard@navarik.com
Subject: Re: Truncate table at a certain size.
In-Reply-To: <dcc563d10804282031m45648f1ar91e6adb10f2343@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