Re: Postgres eats up memory when using cursors

Denis Perchine <dyp@perchine.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