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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Hubert FONGARNAND-2

The following bug has been logged online:

Bug reference:      2069
Logged by:          Hubert FONGARNAND
Email address:      [hidden email]
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 [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
"Hubert FONGARNAND" <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

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

Jaime Casanova
In reply to this post by Hubert FONGARNAND-2
On 11/24/05, Hubert FONGARNAND <[hidden email]> 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