Re: Truncate table at a certain size.
| От | Gurjeet Singh |
|---|---|
| Тема | Re: Truncate table at a certain size. |
| Дата | |
| Msg-id | 65937bea0804281824t6640fc03y4469f129e5ed2e7c@mail.gmail.com обсуждение исходный текст |
| Ответ на | Truncate table at a certain size. (Dana Huggard - Navarik <dhuggard@navarik.com>) |
| Ответы |
Re: Truncate table at a certain size.
|
| Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: