Re: out of memory using Postgres with Spring/Hibernate/Java

maarten roosendaal wrote:
> Hi,
>
> We are currently having a problem that our Postgres DB
> is throwing an SQL error which states that it's 'out
> of memory'.
>
> What we have is a DB with 1 table that has 3.9 million
> records. We need to find certain records that are to
> be processed by a Java App so we do a "select id from
> table where type=a and condition in (1, 2) order by id
> limit 2000". When this query gets executed we see the
> memory on the DB Server increasing and after it has
> finishes it drops a bit but we see it growing a few MB
> per few minutes.

So it's postgresql's memory usage that is increasing?
From what to what?
How many backends are we talking about, and does this happen to all of them?
How much memory does the machine have and what else is using it?
Oh, and what version of PG, JDBC, Spring, Hibernate etc?

> This has caused an out of memory
> after the system has been processing for a day or 2.

Each new connection will start a new backend, so presumably this
connection is continuously active for days.

> The query is heavy because of the order by but that
> does not explain why the memory is increasing.
>
> We use a DAO which extends HibernateDaoSupport and the
> method (findIds) has been marked as
> propagation_required. So we assume Spring manages the
> transaction and thus closing of the resultset.

First step has to be to turn query-logging on for the problem
application. It might be that there is a memory leak in PostgreSQL, but
it might be that something isn't being released properly by the
applicaton libraries.

You can turn statement logging on or off in the postgresql.conf file, or
by issuing "set log_statement=XXX" as a query after connecting. That way
we can see exactly what is happening.

--
Richard Huxton
Archonet Ltd