Thread: cursor already in use error

cursor already in use error

From

"Sim Zacks"

Date:

02 March 2005, 10:23:41

PostGreSQL 8.0beta1
I have a function that uses a cursor and it is giving me the error: cursor
"crsr" already in use when the parameters I pass in come from another table.

The function works fine when I call it by itself, such as select
PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1)
but when I try select PartNeedsReschedule(PartID,1) from Parts then I get
the error.

It seems that when I qualify the query such as PartNeedsReschedule(PartID,1)
from Parts where partid=100
then it works because it is only returning one row.

Is this a known problem with cursor based functions that they can not be
called by set based queries?
Is there a workaround?

Thank You
Sim

Re: cursor already in use error

From

Michael Fuhr

Date:

02 March 2005, 11:03:49

On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote:

> PostGreSQL 8.0beta1

That's pretty old.  Have you tried 8.0.1?

> I have a function that uses a cursor and it is giving me the error: cursor
> "crsr" already in use when the parameters I pass in come from another table.
>
> The function works fine when I call it by itself, such as select
> PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1)
> but when I try select PartNeedsReschedule(PartID,1) from Parts then I get
> the error.

Could you post a simple, self-contained example that demonstrates
the problem?  Please show definitions of Parts and PartNeedsReschedule
and some sample data -- enough that somebody could copy what you
post into a test database and duplicate the problem.  It'll be
easier to help if we can see exactly what you're doing.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: cursor already in use error

From

"Sim Zacks"

Date:

02 March 2005, 12:17:51

I haven't tried 8.0.1 yet. I am planning on making the upgrade soon, but
since I haven't seen this issue discussed I did not think it had been fixed
just because I am using an older version. I did search the Internet and
found one person who had the same issue and found a workaround that won't
apply to my situation.
Here is an example of my problem:

create table test( TestID int primary key, name varchar);
insert into test(TestID,name) values(1,'Bob');
insert into test(testid,name) values(2,'Mark');
insert into test(testid,name) values(3,'Tom');

create table test2(Test2ID serial primary key, TestID int, comments
varchar);
insert into test2(TestID,Comments) values(1,'Comment 1');
insert into test2(TestID,Comments) values(1,'Comment 2');
insert into test2(TestID,Comments) values(1,'Comment 3');
insert into test2(TestID,Comments) values(2,'Comment 1');

create or replace function testcursor(thistestid int) returns varchar as
$$
declare
 crs Cursor for select comments from test a join test2 b on
a.testid=b.testid where a.TestID=thistestid;
 thiscomment varchar;
 totalstr varchar;
begin
 open crs;
 fetch crs into thiscomment;
 totalstr:='';
 while found loop
  totalstr:= totalstr || '-' || thiscomment;
  fetch crs into thiscomment;
 end loop;
 return totalstr;
end;
$$language 'plpgsql';

select name,testcursor(testid) from test; --doesn't work
select name,testcursor(testid) from test where testid=1; -- works (as does
testid=2 or 3)

Obviously in this example I could use a for loop which would remove the
problem, but in my real life complicated example, I cannot use a for loop
and I am stuck using a cursor. My real life example uses the cursor inside
of a for loop such that it opens the cursor before the loop starts and then
based on criteria inside the loop sometimes it reads the values and fetches
the next value and sometimes it doesn't.

Thank You
Sim

"Michael Fuhr" <mike@fuhr.org> wrote in message
news:20050302080327.GA47806@winnie.fuhr.org...
> On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote:
>
> > PostGreSQL 8.0beta1
>
> That's pretty old.  Have you tried 8.0.1?
>
> > I have a function that uses a cursor and it is giving me the error:
cursor
> > "crsr" already in use when the parameters I pass in come from another
table.
> >
> > The function works fine when I call it by itself, such as select
> > PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1)
> > but when I try select PartNeedsReschedule(PartID,1) from Parts then I
get
> > the error.
>
> Could you post a simple, self-contained example that demonstrates
> the problem?  Please show definitions of Parts and PartNeedsReschedule
> and some sample data -- enough that somebody could copy what you
> post into a test database and duplicate the problem.  It'll be
> easier to help if we can see exactly what you're doing.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>

Re: cursor already in use error

From

Michael Fuhr

Date:

02 March 2005, 12:53:53

On Wed, Mar 02, 2005 at 11:15:52AM +0200, Sim Zacks wrote:

> select name,testcursor(testid) from test; --doesn't work
> select name,testcursor(testid) from test where testid=1; -- works (as does
> testid=2 or 3)

If I add "close crs;" before the function returns, I get this:

SELECT name, testcursor(testid) FROM test;
 name |           testcursor
------+--------------------------------
 Bob  | -Comment 1-Comment 2-Comment 3
 Mark | -Comment 1
 Tom  |
(3 rows)

Is that what you're after?  If so, then the problem might simply
be that you're not closing the cursor when you're done with it.
Will closing it work in the real code?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: cursor already in use error

From

Alban Hertroys

Date:

02 March 2005, 12:56:10

Sim Zacks wrote:
> create or replace function testcursor(thistestid int) returns varchar as
> $$
> declare
>  crs Cursor for select comments from test a join test2 b on
> a.testid=b.testid where a.TestID=thistestid;
>  thiscomment varchar;
>  totalstr varchar;
> begin
>  open crs;
>  fetch crs into thiscomment;
>  totalstr:='';
>  while found loop
>   totalstr:= totalstr || '-' || thiscomment;
>   fetch crs into thiscomment;
>  end loop;

close crs;

>  return totalstr;
> end;
> $$language 'plpgsql';
>
> select name,testcursor(testid) from test; --doesn't work
> select name,testcursor(testid) from test where testid=1; -- works (as does
> testid=2 or 3)

The second query works because you fetch only one record; You don't call
the SP a second time with the cursor still open, while you do with the
first query.

Always close your cursors.

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

Re: cursor already in use error

From

"Sim Zacks"

Date:

02 March 2005, 13:04:21

> If I add "close crs;" before the function returns, I get this:
>
> SELECT name, testcursor(testid) FROM test;
>  name |           testcursor
> ------+--------------------------------
>  Bob  | -Comment 1-Comment 2-Comment 3
>  Mark | -Comment 1
>  Tom  |
> (3 rows)
>

You got it.
I was closing the cursor at the end of the function, but there were other
possible returns in the middle.
I changed the code to add close before each return and it works like a
charm.

I rushed together the example, or I should have caught that.

Much thanks.
Sim

Re: cursor already in use error

From

"Sim Zacks"

Date:

02 March 2005, 13:05:16

> Always close your cursors.
>
Thanks.
In my real example I had multiple return points and did not close the cursor
before each one.