Thread: BUG #2069: Cursors are not close outside of a function

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!

"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

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);
>

=46rom 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
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
by the caller, or it will be closed automatically when the transaction clos=
es.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^=
^^^^^^^^^^^^

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

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 ;)