pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTruncate table at a certain size.
3+ messages / 3 participants
[nested] [flat]
* Truncate table at a certain size.
@ 2008-04-28 22:35 Dana Huggard - Navarik <dhuggard@navarik.com>
2008-04-29 01:24 ` Re: Truncate table at a certain size. Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dana Huggard - Navarik @ 2008-04-28 22:35 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Truncate table at a certain size.
2008-04-28 22:35 Truncate table at a certain size. Dana Huggard - Navarik <dhuggard@navarik.com>
@ 2008-04-29 01:24 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2008-04-29 03:31 ` Re: Truncate table at a certain size. Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gurjeet Singh @ 2008-04-29 01:24 UTC (permalink / raw)
To: Dana Huggard - Navarik <dhuggard@navarik.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Truncate table at a certain size.
2008-04-28 22:35 Truncate table at a certain size. Dana Huggard - Navarik <dhuggard@navarik.com>
2008-04-29 01:24 ` Re: Truncate table at a certain size. Gurjeet Singh <singh.gurjeet@gmail.com>
@ 2008-04-29 03:31 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2008-04-29 03:31 UTC (permalink / raw)
To: Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: Dana Huggard - Navarik <dhuggard@navarik.com>; pgsql-sql
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-04-29 03:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-28 22:35 Truncate table at a certain size. Dana Huggard - Navarik <dhuggard@navarik.com>
2008-04-29 01:24 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2008-04-29 03:31 ` Scott Marlowe <scott.marlowe@gmail.com>
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