The following bug has been logged online:

Bug reference: 2069
Logged by: Hubert FONGARNAND
Email address: h.fongarnand@fiducial.fr
PostgreSQL version: 8.1
Operating system: Linux
Description: Cursors are not close outside of a function
Details:

I've written a little test function :

CREATE OR REPLACE FUNCTION test()
RETURNS void AS
$BODY$declare
c1 cursor for select * from essai;
c1_rec record;
begin
open c1;
fetch c1 into c1_rec;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Note that i don't close c1 after returning...
(c1 should be close automatically because it is declared inside my
function);

If i execute :
select test();
select test();

the second select fails with :
ERROR: cursor "c1" already in use
CONTEXTE : PL/pgSQL function "test" line 5 at open

not that if I use to distinct connection it doesn't fails... It seems that a
cursor is a "global" variable inside a "postgres" process (1 connection).

It should be a bug!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Hubert FONGARNAND" <h.fongarnand@fiducial.fr> writes:
> Note that i don't close c1 after returning...
> (c1 should be close automatically because it is declared inside my
> function);

What makes you think that?

If we forcibly closed cursors at function exit, it'd be impossible to
return a cursor reference, which is a very handy feature.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 11/24/05, Hubert FONGARNAND <h.fongarnand@fiducial.fr> wrote:
> Note that i don't close c1 after returning...
> (c1 should be close automatically because it is declared inside my
> function);
>

From the manual (signs are mine)...

35.8.3.3. Returning Cursors
PL/pgSQL functions can return cursors to the caller. This is useful to
return multiple rows or columns, especially with very large result
sets. To do this, the function opens the cursor and returns the cursor
name to the caller (or simply opens the cursor using a portal name
specified by or otherwise known to the caller). The caller can then
fetch rows from the cursor. The cursor can be closed

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
by the caller, or it will be closed automatically when the transaction closes.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-----------------------------------

This is the behaviour i see in informix (i dunno for other major
databases)... Cursors closes at end of transaction or by the user...



--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings