Re: [GENERAL] cursors

Not that I believe this might cause the problem, but isn't
the syntax for fetch

"fetch 1000 in cursor_name" instead of "from cursor_name"?

We've been using this syntax for quite some time on
a system that has way more data than would be successfully
held in memory.

Also, we've found in the past that making sure the database
is vacuumed on a somewhat regular basis helps with memory
problems.

Cheers, Thomas

John Henderson wrote:
>
> Hi,
> I am using postgres 6.4 on bsd/os3.0
> The select that I want to use:
> $select="select user_name,start,stop,nas_ip,port,
>                         date_part('epoch',sess_time)
>                         from $sessions_table s, userdir u
>                         where user_name=username
>                         and services in $in_phrase";
> runs out of memory.
> So I cleverly broke it down using cursors....
> $result = $conn->exec("BEGIN");
> $result = $conn->exec("declare cursor1 cursor for $select");
> $result = $conn->exec("fetch 1000 from cursor1");
>
> NOT SO CLEVER says the log
> query: fetch 1000 from cursor1
> ProcessUtility: fetch 1000 from cursor1
> FATAL 1:  palloc failure: memory exhausted
> proc_exit(0) [#0]
> shmem_exit(0) [#0]
> exit(0)
>
> Questions:
> So, correct me if I'm wrong but it looks like cursors do not actually save
> any memory and I have to use SELECT ... LIMIT ... OFFSET for this and to do
> that I have to upgrade from postgresql6.4
>
> Or is there another way?
>
> And, it seems to me that there is a problem with pg_dump from 6.4 and reload
> the data into 6.5.3 because of some sort of structural changes. Where is the
> documentation that will safely take me from 6.4 to 6.5.3?
>
> Thanks,
> John
>
> ************

--
------------------------------------------------------------
Thomas Reinke                            Tel: (905) 331-2260
Director of Technology                   Fax: (905) 331-2504
E-Soft Inc.                         http://www.e-softinc.com