Re: [GENERAL] cursor already in use error
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cursor already in use error
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] cursor already in use error
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: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] cursor already in use error
> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cursor already in use error
> Always close your cursors. > Thanks. In my real example I had multiple return points and did not close the cursor before each one. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cursor already in use error
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/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
