Thread: Memory Usage

I am having a small issue with PostgreSQL 7.0.3 on FreeBSD 4.2 Stable.  When
I perform an update on a table with roughly 2 million rows, the postgres
process starts eating up memory until it eventually uses up all of the
memory and exits without finishing.  I have also seen the same thing happen
in complex SELECT statements that I have run.  The update statement is as
follows:

UPDATE pages SET createdtime = NOW();

Is there a reason why this would take up all of the memory??  Also the
select statement that caused the issue was a SELECT and GROUP BY that was in
the form of:

SELECT COUNT(*) AS hitcount, date_part('yy', createdtime) AS Year,
date_part('mm', createdtime) AS Month, date_part('dd', createdtime) AS Day
FROM Log group by Year, Month, Day

I found a better way to do this afterwards, but why wouldn't the database
utilize temporary tables to complete this query without using all the RAM?

---------------------------------------------
Nathan Barnett

"Nathan Barnett" <nbarnett@cellularphones.com> writes:
> I am having a small issue with PostgreSQL 7.0.3 on FreeBSD 4.2 Stable.  When
> I perform an update on a table with roughly 2 million rows, the postgres
> process starts eating up memory until it eventually uses up all of the
> memory and exits without finishing.  I have also seen the same thing happen
> in complex SELECT statements that I have run.  The update statement is as
> follows:

> UPDATE pages SET createdtime = NOW();

The now() function invocation leaks memory --- only a dozen or two bytes
per invocation, I think, but it'll still add up over that many rows.
You'd see the same sort of problem with function calls or operations on
any pass-by-reference datatype.

This is fixed for 7.1...

            regards, tom lane