Re: Postgres eats up memory when using cursors

> Looks pretty simple to me too; I was expecting that you were doing
> expression evaluations or some such.  I cannot reproduce any backend
> leakage in this query, either in 7.0.* or current sources.  I did
>
> create table senders (email text, first_name text, last_name text);
> \copy senders from 'todo'        -- some junk data
> create index sendersi on senders(email);
> insert into senders select * from senders;  -- repeat a few times
>
> begin;
> explain
> declare senders_c cursor for select email, first_name, last_name
> from senders order by email;
> NOTICE:  QUERY PLAN:
>
> Index Scan using sendersi on senders  (cost=0.00..102.78 rows=2161
> width=36)
>
> end;
>
> then made a script file
>
> begin;
> declare senders_c cursor for select email, first_name, last_name
> from senders order by email;
>
> fetch 10000 from senders_c
> \g zzz
> fetch backward 10000 from senders_c
> \g zzz
> fetch 10000 from senders_c
> \g zzz
> fetch backward 10000 from senders_c
> \g zzz
> fetch 10000 from senders_c
> \g zzz
> ... repeat a few thousand times ...
>
> end;
>
> Backend memory usage is absolutely rock steady.
>
> Curiously, there does seem to be a leak in psql, or possibly libpq,
> when running this script.  It's per-query not per-tuple, so it's
> easier to see if you make the fetches shorter:
>
> fetch 100 from senders_c
> \g zzz
> fetch backward 100 from senders_c
> \g zzz
>
> Repeating these like mad, psql grows about 1Kb/sec on my machine.
> This occurs with current sources but NOT with 7.0.2 psql.  Peter,
> any thoughts about that?

Actually you should also consider that there is really intensive inserts
happend at the same time. Cursor is closed in 3-4 days only...

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------