Fwd: High consumns memory

2009/7/1 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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