Re: Truncate table at a certain size.

------=_Part_10286_29401046.1209432262939
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik <
dhuggard@[EMAIL PROTECTED]
> 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]@[EMAIL PROTECTED]
 gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

------=_Part_10286_29401046.1209432262939
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik &lt;<a
href="mailto:dhuggard@[EMAIL PROTECTED]
">dhuggard@[EMAIL PROTECTED]
>&gt;
wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt
0.8ex; padding-left: 1ex;">
Hello,<br>
<br>
What would be the best method to truncate a table once it reaches a<br>
certain size.<br>
<br>
For instance, a table named log. &nbsp;I can check the size of the
log;<br>
<br>
 &nbsp; db=# select pg_relation_size(&#39;log&#39;);<br>
 &nbsp; pg_relation_size<br>
 &nbsp; ------------------<br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8192<br>
 &nbsp; (1 row)<br>
<br>
<br>
What I would like to do is<br>
<br>
If table log, is greater than xxxx bytes<br>
 &nbsp; then truncate table log<br>
</blockquote><div><br>No, you wouldn&#39;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&#39;t really
be sure which part you want to truncate.<br>
<br>But by design, in an ever growing table, Postgres puts new rows at the
tail end; so even with this knowledge you don&#39;t want to truncate the
table, because your older rows are towards the head/beginning of th table
and not at the tail.<br>
<br></div><blockquote class="gmail_quote" style="border-left: 1px solid
rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Maybe I&#39;ll dump it to a flat file first, (yes, it should really be
a<br>
flat file to start with, and not in a
db.)<br><br></blockquote><div><br>Yes, that&#39;d be a better approach;
use two files of fixed size, and cycle between them (much like how
Postgres deals with it&#39;s transaction logs (in pg_xlog/)).<br>
<br>Best regards,<br></div></div>--
<br>gurjeet[.singh]@[EMAIL PROTECTED]
>singh.gurjeet@[EMAIL PROTECTED]
 gmail | hotmail |
indiatimes | yahoo }.com<br><br>EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br>
<br>Mail sent from my BlackLaptop device

------=_Part_10286_29401046.1209432262939--