Re: Postgres eats up memory when using cursors

Denis Perchine <dyp(at)perchine(dot)com> writes:
> I declare a cursor on the table of approx. 1 million rows.
> And start fetching data by 1000 rows at each fetch.
> Data processing can take quite a long time (3-4 days)
> Theoretically postgres process should remain the same in size.
> But it grows... In the end of 3rd day it becames 256Mb large!!!!

> declare senders_c cursor for select email, first_name, last_name from senders
> order by email

> fetch 1000 from senders_c

> db=# explain declare senders_c cursor for select email, first_name, last_name
> from senders order by email;
> NOTICE: QUERY PLAN:

> Index Scan using senders_email_key on senders (cost=0.00..197005.37
> rows=928696 width=36)

> db=# \d senders
> Table "senders"
> Attribute | Type | Modifier
> ------------+-----------+----------
> email | text |
> first_name | text |
> last_name | text |
> stamp | timestamp |
> Index: senders_email_key

> db=# \d senders_email_key
> Index "senders_email_key"
> Attribute | Type
> -----------+------
> email | text
> unique btree

> That's all. I could not imagine anything more simple...

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?

regards, tom lane