2009/7/1 Albe Laurenz <laurenz.albe@wien.gv.at>
>
> Anderson Valadares wrote:
> [this is on Windows, DB is accessed with ODBC driver 8.4.3]
>
> > Thanks for the answer ...
> >
> > But honestly I think that was a misunderstood.
> >
> >
> >
> > The memory increase issue is showed in the DATA column.
> >
> > Look how day by day it increases exponencially.
> >
> > In a few days PostGres goes out of memory, close the
> > connections and enter in a recovery mode.
> >
> > I really don’t know what is causing it.
> >
> >
> >
> >
> > Date 29/06/2009
> >
> > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
> > 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test
10.255.100.73(4796)SELECT
> >
> > Date 29/06/2009
> >
> > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
> > 9943 postgres 15 0 994m 33m 960m 818m 143m 3540 S 29.5 23.9 48:19.96 postgres: dbtest test
10.255.100.73(4796)idle
> >
> > Date 29/06/2009
> >
> > PID USER PR NI VIRT RES SHR CODE DATA S %CPU %MEM TIME+ COMMAND
> > 9943 postgres 16 0 1366m 1.3g 818m 3540 515m S 31.2 33.2 192:20.61 postgres: dbtest test 10.255.100.73(4796)
SELECT
> >
> > Date 30/06/2009
> >
> > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
> > 9943 postgres 17 0 1724m 30m 1.7g 821m 873m 3540 R 27.2 42.2 325:54.83 postgres: citgis citgis
10.255.100.73(4796)SELECT
>
> Now that is weird.
> How can the same backend process suddenly be connected to database "citgis" as user "citgis"?
> Do you have an explanation?
>
> What is your work_mem setting?
> This influences the amount of "private" memory a backend will allocate.
>
> Can you say more that "executes a PL/pgSQL function in a loop" about the workload?
> Are there long transactions?
>
> Which version of PostgreSQL is this?
>
> Yours,
> Laurenz Albe
Answering ...
1. PostGre SQL 8.3.6
2. Work_mem – 5MB
3. About the connection citgis citgis ... it was my mistake. Is
the same database and the same user, I was renaming to dbtest teste
only for security ...
4. About the “function loop”. I have a windows service that
execute a select (limit 200) each 500ms and after that it calls a
PostGre procedure (developed for us).
for each row returned in my select.
Thanks again