Thread: DECLARE CURSOR
Hello, When I call DECLARE CURSOR out of transaction command success, but cursor is not created Reference manual say that this get error : ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks I don't find this text in pgsql source code What is problem ? Thanks Haris Peco
On Fri, 15 Nov 2002, snpe wrote: > Hello, > When I call DECLARE CURSOR out of transaction command success, > but cursor is not created > Reference manual say that this get error : > ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks > I don't find this text in pgsql source code > What is problem ? I get that error text in 7.3b2. Don't have an earlier version available right at the moment to test. It may very well be making the cursor, but IIRC the cursor would have gone away at the end of the implicit transaction wrapping the statement.
On Fri, 15 Nov 2002, snpe wrote:
> Hello,
> When I call DECLARE CURSOR out of transaction command success,
> but cursor is not created
> Reference manual say that this get error :
> ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks
> I don't find this text in pgsql source code
> What is problem ?
According to the documentation for DECLARE CURSOR (v.7.2.x):
"Cursors are only available in transactions. Use to BEGIN, COMMIT and
ROLLBACK to define a transaction block."
This seems consistent with your error message. Please try
wrapping your DECLARE inside a transaction using BEGIN,...
HTH--
-frank
On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: > On Fri, 15 Nov 2002, snpe wrote: > > Hello, > > When I call DECLARE CURSOR out of transaction command success, > > but cursor is not created > > Reference manual say that this get error : > > ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks > > I don't find this text in pgsql source code > > What is problem ? > > According to the documentation for DECLARE CURSOR (v.7.2.x): > > "Cursors are only available in transactions. Use to BEGIN, COMMIT and > ROLLBACK to define a transaction block." > > This seems consistent with your error message. Please try > wrapping your DECLARE inside a transaction using BEGIN,... > I understand it. I don't understand why 'DECLARE CURSOR' success out of a transaction - I expect error regards haris peco
On Sat, 16 Nov 2002, snpe wrote:
> On Saturday 16 November 2002 09:29 pm, Frank Miles wrote:
> > On Fri, 15 Nov 2002, snpe wrote:
> > > Hello,
> > > When I call DECLARE CURSOR out of transaction command success,
> > > but cursor is not created
> > > Reference manual say that this get error :
> > > ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks
> > > I don't find this text in pgsql source code
> > > What is problem ?
> >
> > According to the documentation for DECLARE CURSOR (v.7.2.x):
> >
> > "Cursors are only available in transactions. Use to BEGIN, COMMIT and
> > ROLLBACK to define a transaction block."
> >
> > This seems consistent with your error message. Please try
> > wrapping your DECLARE inside a transaction using BEGIN,...
> >
>
> I understand it.
> I don't understand why 'DECLARE CURSOR' success out of a transaction
> - I expect error
What version are you using? At least with 7.2.x, there is an immediate
error at the DECLARE statement. Perhaps I am misunderstanding your
question?
-frank
On Sunday 17 November 2002 05:46 am, Frank Miles wrote: > On Sat, 16 Nov 2002, snpe wrote: > > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: > > > On Fri, 15 Nov 2002, snpe wrote: > > > > Hello, > > > > When I call DECLARE CURSOR out of transaction command success, > > > > but cursor is not created > > > > Reference manual say that this get error : > > > > ERROR: DECLARE CURSOR may only be used in begin/end transaction > > > > blocks I don't find this text in pgsql source code > > > > What is problem ? > > > > > > According to the documentation for DECLARE CURSOR (v.7.2.x): > > > > > > "Cursors are only available in transactions. Use to BEGIN, COMMIT and > > > ROLLBACK to define a transaction block." > > > > > > This seems consistent with your error message. Please try > > > wrapping your DECLARE inside a transaction using BEGIN,... > > > > I understand it. > > I don't understand why 'DECLARE CURSOR' success out of a transaction > > - I expect error > > What version are you using? At least with 7.2.x, there is an immediate > error at the DECLARE statement. Perhaps I am misunderstanding your > question? > 7.3b5 maybe, it is prepare for cursor out of a transaction (I hope) regards Haris Peco
On Sun, 17 Nov 2002 06:06:05 -0600, snpe wrote: > On Sunday 17 November 2002 05:46 am, Frank Miles wrote: >> On Sat, 16 Nov 2002, snpe wrote: >> > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: >> > > On Fri, 15 Nov 2002, snpe wrote: >> > > > Hello, >> > > > When I call DECLARE CURSOR out of transaction command success, >> > > > but cursor is not created >> > > > Reference manual say that this get error : >> > > > ERROR: DECLARE CURSOR may only be used in begin/end transaction >> > > > blocks I don't find this text in pgsql source code What is >> > > > problem ? >> > > >> > > According to the documentation for DECLARE CURSOR (v.7.2.x): >> > > >> > > "Cursors are only available in transactions. Use to BEGIN, COMMIT >> > > and >> > > ROLLBACK to define a transaction block." >> > > >> > > This seems consistent with your error message. Please try wrapping >> > > your DECLARE inside a transaction using BEGIN,... >> > >> > I understand it. >> > I don't understand why 'DECLARE CURSOR' success out of a transaction >> > - I expect error >> >> What version are you using? At least with 7.2.x, there is an immediate >> error at the DECLARE statement. Perhaps I am misunderstanding your >> question? >> >> > 7.3b5 > maybe, it is prepare for cursor out of a transaction (I hope) > > I'm getting a little confused, here, reading this. I don't have a BEGIN, COMMIT, or ROLLBACK in sight in my ESQL application, but my cursor works just fine. Under which circumstances are the BEGIN, COMMIT, and ROLLBACK required? Is that something specific to the C interface? -- Matthew Vanecek perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);' ******************************************************************************** For 93 million miles, there is nothing between the sun and my shadow except me. I'm always getting in the way of something...
On Monday 18 November 2002 02:27 am, \"Matthew V.\ wrote: > On Sun, 17 Nov 2002 06:06:05 -0600, snpe wrote: > > On Sunday 17 November 2002 05:46 am, Frank Miles wrote: > >> On Sat, 16 Nov 2002, snpe wrote: > >> > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: > >> > > On Fri, 15 Nov 2002, snpe wrote: > >> > > > Hello, > >> > > > When I call DECLARE CURSOR out of transaction command success, > >> > > > but cursor is not created > >> > > > Reference manual say that this get error : > >> > > > ERROR: DECLARE CURSOR may only be used in begin/end transaction > >> > > > blocks I don't find this text in pgsql source code What is > >> > > > problem ? > >> > > > >> > > According to the documentation for DECLARE CURSOR (v.7.2.x): > >> > > > >> > > "Cursors are only available in transactions. Use to BEGIN, COMMIT > >> > > and > >> > > ROLLBACK to define a transaction block." > >> > > > >> > > This seems consistent with your error message. Please try wrapping > >> > > your DECLARE inside a transaction using BEGIN,... > >> > > >> > I understand it. > >> > I don't understand why 'DECLARE CURSOR' success out of a transaction > >> > - I expect error > >> > >> What version are you using? At least with 7.2.x, there is an immediate > >> error at the DECLARE statement. Perhaps I am misunderstanding your > >> question? > > > > 7.3b5 > > maybe, it is prepare for cursor out of a transaction (I hope) > > I'm getting a little confused, here, reading this. I don't have a BEGIN, > COMMIT, or ROLLBACK in sight in my ESQL application, but my cursor works > just fine. Under which circumstances are the BEGIN, COMMIT, and ROLLBACK > required? Is that something specific to the C interface? You don't use cursor, probably. For PostgreSQL cursor is explicit with DECLARE CURSOR in sql command It is like : BEGIN;; .. DECLARE c1 CURSOR FOR SELECT ...; ... FETCH 1 FROM c1 ... COMMIT; regards Haris Peco
On Sun, 17 Nov 2002, snpe wrote: > On Sunday 17 November 2002 05:46 am, Frank Miles wrote: > > On Sat, 16 Nov 2002, snpe wrote: > > > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: > > > > On Fri, 15 Nov 2002, snpe wrote: > > > > > Hello, > > > > > When I call DECLARE CURSOR out of transaction command success, > > > > > but cursor is not created > > > > > Reference manual say that this get error : > > > > > ERROR: DECLARE CURSOR may only be used in begin/end transaction > > > > > blocks I don't find this text in pgsql source code > > > > > What is problem ? > > > > > > > > According to the documentation for DECLARE CURSOR (v.7.2.x): > > > > > > > > "Cursors are only available in transactions. Use to BEGIN, COMMIT and > > > > ROLLBACK to define a transaction block." > > > > > > > > This seems consistent with your error message. Please try > > > > wrapping your DECLARE inside a transaction using BEGIN,... > > > > > > I understand it. > > > I don't understand why 'DECLARE CURSOR' success out of a transaction > > > - I expect error > > > > What version are you using? At least with 7.2.x, there is an immediate > > error at the DECLARE statement. Perhaps I am misunderstanding your > > question? > > > > 7.3b5 > maybe, it is prepare for cursor out of a transaction (I hope) No, you just have autocommit turned off. Which means that the second you connect and type a command, Postgresql does an invisible begin for you. I.e. you're ALWAYS in an uncommitted transaction. Note that you'll have to issue a commit to get your changes into the database.
On Monday 18 November 2002 05:13 pm, scott.marlowe wrote: > On Sun, 17 Nov 2002, snpe wrote: > > On Sunday 17 November 2002 05:46 am, Frank Miles wrote: > > > On Sat, 16 Nov 2002, snpe wrote: > > > > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: > > > > > On Fri, 15 Nov 2002, snpe wrote: > > > > > > Hello, > > > > > > When I call DECLARE CURSOR out of transaction command success, > > > > > > but cursor is not created > > > > > > Reference manual say that this get error : > > > > > > ERROR: DECLARE CURSOR may only be used in begin/end transaction > > > > > > blocks I don't find this text in pgsql source code > > > > > > What is problem ? > > > > > > > > > > According to the documentation for DECLARE CURSOR (v.7.2.x): > > > > > > > > > > "Cursors are only available in transactions. Use to BEGIN, COMMIT > > > > > and ROLLBACK to define a transaction block." > > > > > > > > > > This seems consistent with your error message. Please try > > > > > wrapping your DECLARE inside a transaction using BEGIN,... > > > > > > > > I understand it. > > > > I don't understand why 'DECLARE CURSOR' success out of a transaction > > > > - I expect error > > > > > > What version are you using? At least with 7.2.x, there is an immediate > > > error at the DECLARE statement. Perhaps I am misunderstanding your > > > question? > > > > 7.3b5 > > maybe, it is prepare for cursor out of a transaction (I hope) > > No, you just have autocommit turned off. Which means that the second you > connect and type a command, Postgresql does an invisible begin for you. > I.e. you're ALWAYS in an uncommitted transaction. Note that you'll have > to issue a commit to get your changes into the database. > > I want do next : table - big table and select work with cursor only I select row and if any condition is true I do transaction on another table I can't do all in one transaction (performance reason) - for some rows in table I do transaction How can I do this ? Thanks Haris Peco
On Mon, 18 Nov 2002 08:28:59 -0600, Haris Peco wrote: > On Monday 18 November 2002 02:27 am, \"Matthew V.\ wrote: >> On Sun, 17 Nov 2002 06:06:05 -0600, snpe wrote: >> > On Sunday 17 November 2002 05:46 am, Frank Miles wrote: >> >> On Sat, 16 Nov 2002, snpe wrote: >> >> > On Saturday 16 November 2002 09:29 pm, Frank Miles wrote: >> >> > > On Fri, 15 Nov 2002, snpe wrote: >> >> > > > Hello, >> >> > > > When I call DECLARE CURSOR out of transaction command >> >> > > > success, >> >> > > > but cursor is not created >> >> > > > Reference manual say that this get error : >> >> > > > ERROR: DECLARE CURSOR may only be used in begin/end >> >> > > > transaction blocks I don't find this text in pgsql source code >> >> > > > What is problem ? >> >> > > >> >> > > According to the documentation for DECLARE CURSOR (v.7.2.x): >> >> > > >> >> > > "Cursors are only available in transactions. Use to BEGIN, >> >> > > COMMIT and >> >> > > ROLLBACK to define a transaction block." >> >> > > >> >> > > This seems consistent with your error message. Please try >> >> > > wrapping your DECLARE inside a transaction using BEGIN,... >> >> > >> >> > I understand it. >> >> > I don't understand why 'DECLARE CURSOR' success out of a >> >> > transaction - I expect error >> >> >> >> What version are you using? At least with 7.2.x, there is an >> >> immediate error at the DECLARE statement. Perhaps I am >> >> misunderstanding your question? >> > >> > 7.3b5 >> > maybe, it is prepare for cursor out of a transaction (I hope) >> >> I'm getting a little confused, here, reading this. I don't have a >> BEGIN, COMMIT, or ROLLBACK in sight in my ESQL application, but my >> cursor works just fine. Under which circumstances are the BEGIN, >> COMMIT, and ROLLBACK required? Is that something specific to the C >> interface? > > You don't use cursor, probably. > For PostgreSQL cursor is explicit with DECLARE CURSOR in sql command It > is like : > BEGIN;; > .. > DECLARE c1 CURSOR FOR SELECT ...; > ... > FETCH 1 FROM c1 > ... > COMMIT; > > > Yes, I do use a cursor. The ESQL I mentioned means "Embedded SQL" (sorry, thought everyone knew). Cursors are a very big part of ESQL. But I don't have any BEGINS, or COMMITS (why would I? I do SELECTs, not INSERTs/UPDATEs/DELETEs!). The cursor declaration, and the subsequent FETCHes, work just fine. That's why I was wondering if I was missing something. The cursor works perfectly the way I wrote it, yet people in this thread keep talking like cursors are only declareable/useable inside transactions (BEGIN-COMMIT blocks).. I personally don't see why you would want to waste transaction overhead unless you are modifying the data (especially since Postgresql doesn't support updateable cursors). In any case, whether or not it's the "correct" behavior, you don't need to specify a BEGIN/COMMIT block to DECLARE a cursor. The documentation the original poster quoted appears to be in error, or outdated (I have the same docs, and they don't match with actual behavior). I declare my cursor in an include file (so that it's global to the file), open the cursor, fetch the cursor until EOF or other error, and process the data. I don't "EXEC SQL BEGIN;" or anything anywhere. Since I'm doing FETCHes, there's no need for a COMMIT. I was just wondering what the hullabaloo was all about, because I don't get any of the errors described by previous posters, and thought maybe I accidentally fixed something, or broke something that was supposed to break my DECLARE... I tried mucking around with autocommit = off/on, but that affects neither the DECLARE nor the FETCH. Is there supposed to be a global autocommit setting? I couldn't find one in the docs for 7.2.1. -- Matthew Vanecek perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);' ******************************************************************************** For 93 million miles, there is nothing between the sun and my shadow except me. I'm always getting in the way of something...