Horton

Thread

  1. DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-15T17:05:32Z

    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

  2. Re: DECLARE CURSOR

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-11-16T20:14:17Z

    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.

  3. Re: DECLARE CURSOR

    Frank Miles <fpm@u.washington.edu> — 2002-11-16T21:29:25Z

    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

  4. Re: DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-16T22:32:13Z

    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

  5. Re: DECLARE CURSOR

    Frank Miles <fpm@u.washington.edu> — 2002-11-17T05:46:41Z

    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

  6. Re: DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-17T12:08:18Z

    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

  7. Re: [GENERAL] DECLARE CURSOR

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-17T19:33:05Z

    snpe <snpe@snpe.co.yu> writes:
>   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 

Oops.  I removed that test on 21-Oct as part of this fix:

2002-10-21 18:06  tgl

	* src/: backend/access/transam/xact.c, backend/catalog/heap.c,
	backend/catalog/index.c, backend/commands/dbcommands.c,
	backend/commands/indexcmds.c, backend/commands/tablecmds.c,
	backend/commands/vacuum.c, backend/parser/analyze.c,
	include/access/xact.h: Fix places that were using
	IsTransactionBlock() as an (inadequate) check that they'd get to
	commit immediately on finishing.  There's now a centralized routine
	PreventTransactionChain() that implements the necessary tests.

My reasons for removing it were (a) it was in the wrong place (analyze.c
is not the right place to test execution-time constraints), and (b) it
was the wrong test: the test as written was just IsTransactionBlock(),
which is wrong in the case of autocommit-off, since a DECLARE CURSOR
will start a new transaction perfectly well.  Another objection is that
inside a function call, it ought to be legal to do DECLARE CURSOR even
if we're not in a transaction block, since the function might intend to
use the cursor itself before returning.

I think I had intended to put together an alternative test that only
complained about interactive DECLARE CURSOR and understood about
autocommit, but I forgot.

At this point we can either add the fixed-up error check (meaning RC1
won't be the release after all), or change the documentation.

Comments?

			regards, tom lane

  8. Re: [GENERAL] DECLARE CURSOR

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-17T23:44:58Z

    Let's just fix it and roll an RC2 with the fix.  If not, we can just fix
it in 7.3.1 but I see little problem in rolling an RC2.

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

Tom Lane wrote:
> snpe <snpe@snpe.co.yu> writes:
> >   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 
> 
> Oops.  I removed that test on 21-Oct as part of this fix:
> 
> 2002-10-21 18:06  tgl
> 
> 	* src/: backend/access/transam/xact.c, backend/catalog/heap.c,
> 	backend/catalog/index.c, backend/commands/dbcommands.c,
> 	backend/commands/indexcmds.c, backend/commands/tablecmds.c,
> 	backend/commands/vacuum.c, backend/parser/analyze.c,
> 	include/access/xact.h: Fix places that were using
> 	IsTransactionBlock() as an (inadequate) check that they'd get to
> 	commit immediately on finishing.  There's now a centralized routine
> 	PreventTransactionChain() that implements the necessary tests.
> 
> My reasons for removing it were (a) it was in the wrong place (analyze.c
> is not the right place to test execution-time constraints), and (b) it
> was the wrong test: the test as written was just IsTransactionBlock(),
> which is wrong in the case of autocommit-off, since a DECLARE CURSOR
> will start a new transaction perfectly well.  Another objection is that
> inside a function call, it ought to be legal to do DECLARE CURSOR even
> if we're not in a transaction block, since the function might intend to
> use the cursor itself before returning.
> 
> I think I had intended to put together an alternative test that only
> complained about interactive DECLARE CURSOR and understood about
> autocommit, but I forgot.
> 
> At this point we can either add the fixed-up error check (meaning RC1
> won't be the release after all), or change the documentation.
> 
> Comments?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  9. Re: [GENERAL] DECLARE CURSOR

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-17T23:58:02Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Let's just fix it and roll an RC2 with the fix.  If not, we can just fix
> it in 7.3.1 but I see little problem in rolling an RC2.

Since Marc hasn't yet announced RC1, I think we could get away with just
a quick fix and re-roll of RC1 ...

			regards, tom lane

> ---------------------------------------------------------------------------

> Tom Lane wrote:
>> snpe <snpe@snpe.co.yu> writes:
> 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 
>> 
>> Oops.  I removed that test on 21-Oct as part of this fix:
>> 
>> 2002-10-21 18:06  tgl
>> 
>> * src/: backend/access/transam/xact.c, backend/catalog/heap.c,
>> backend/catalog/index.c, backend/commands/dbcommands.c,
>> backend/commands/indexcmds.c, backend/commands/tablecmds.c,
>> backend/commands/vacuum.c, backend/parser/analyze.c,
>> include/access/xact.h: Fix places that were using
>> IsTransactionBlock() as an (inadequate) check that they'd get to
>> commit immediately on finishing.  There's now a centralized routine
>> PreventTransactionChain() that implements the necessary tests.
>> 
>> My reasons for removing it were (a) it was in the wrong place (analyze.c
>> is not the right place to test execution-time constraints), and (b) it
>> was the wrong test: the test as written was just IsTransactionBlock(),
>> which is wrong in the case of autocommit-off, since a DECLARE CURSOR
>> will start a new transaction perfectly well.  Another objection is that
>> inside a function call, it ought to be legal to do DECLARE CURSOR even
>> if we're not in a transaction block, since the function might intend to
>> use the cursor itself before returning.
>> 
>> I think I had intended to put together an alternative test that only
>> complained about interactive DECLARE CURSOR and understood about
>> autocommit, but I forgot.
>> 
>> At this point we can either add the fixed-up error check (meaning RC1
>> won't be the release after all), or change the documentation.
>> 
>> Comments?
>> 
>> regards, tom lane
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>> 

> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  10. Re: [GENERAL] DECLARE CURSOR

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-18T00:30:31Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Let's just fix it and roll an RC2 with the fix.  If not, we can just fix
> it in 7.3.1 but I see little problem in rolling an RC2.

Here is the patch I am testing (in current sources; I don't think it
needs any adjustments for REL7_3, but haven't tried to apply it yet).
Basically it moves the test that was originally done in parse/analyze.c
into the execution-time setup of a cursor, and enlarges the test to
understand about autocommit-off and inside-a-function exceptions.
Anyone see a problem?

			regards, tom lane

*** src/backend/access/transam/xact.c.orig	Wed Nov 13 10:51:46 2002
--- src/backend/access/transam/xact.c	Sun Nov 17 19:10:20 2002
***************
*** 1488,1493 ****
--- 1488,1537 ----
  	}
  }
  
+ /* --------------------------------
+  *	RequireTransactionChain
+  *
+  *	This routine is to be called by statements that must run inside
+  *	a transaction block, because they have no effects that persist past
+  *	transaction end (and so calling them outside a transaction block
+  *	is presumably an error).  DECLARE CURSOR is an example.
+  *
+  *	If we appear to be running inside a user-defined function, we do not
+  *	issue an error, since the function could issue more commands that make
+  *	use of the current statement's results.  Thus this is an inverse for
+  *	PreventTransactionChain.
+  *
+  *	stmtNode: pointer to parameter block for statement; this is used in
+  *	a very klugy way to determine whether we are inside a function.
+  *	stmtType: statement type name for error messages.
+  * --------------------------------
+  */
+ void
+ RequireTransactionChain(void *stmtNode, const char *stmtType)
+ {
+ 	/*
+ 	 * xact block already started?
+ 	 */
+ 	if (IsTransactionBlock())
+ 		return;
+ 	/*
+ 	 * Are we inside a function call?  If the statement's parameter block
+ 	 * was allocated in QueryContext, assume it is an interactive command.
+ 	 * Otherwise assume it is coming from a function.
+ 	 */
+ 	if (!MemoryContextContains(QueryContext, stmtNode))
+ 		return;
+ 	/*
+ 	 * If we are in autocommit-off mode then it's okay, because this
+ 	 * statement will itself start a transaction block.
+ 	 */
+ 	if (!autocommit && !suppressChain)
+ 		return;
+ 	/* translator: %s represents an SQL statement name */
+ 	elog(ERROR, "%s may only be used in begin/end transaction blocks",
+ 		 stmtType);
+ }
+ 
  
  /* ----------------------------------------------------------------
   *					   transaction block support
*** /home/postgres/pgsql/src/backend/tcop/pquery.c.orig	Wed Sep  4 17:30:43 2002
--- /home/postgres/pgsql/src/backend/tcop/pquery.c	Sun Nov 17 19:10:26 2002
***************
*** 161,166 ****
--- 161,168 ----
  			/* If binary portal, switch to alternate output format */
  			if (dest == Remote && parsetree->isBinary)
  				dest = RemoteInternal;
+ 			/* Check for invalid context (must be in transaction block) */
+ 			RequireTransactionChain((void *) parsetree, "DECLARE CURSOR");
  		}
  		else if (parsetree->into != NULL)
  		{
*** /home/postgres/pgsql/src/include/access/xact.h.orig	Wed Nov 13 10:52:07 2002
--- /home/postgres/pgsql/src/include/access/xact.h	Sun Nov 17 19:10:13 2002
***************
*** 115,120 ****
--- 115,121 ----
  extern void UserAbortTransactionBlock(void);
  extern void AbortOutOfAnyTransaction(void);
  extern void PreventTransactionChain(void *stmtNode, const char *stmtType);
+ extern void RequireTransactionChain(void *stmtNode, const char *stmtType);
  
  extern void RecordTransactionCommit(void);

  11. Re: DECLARE CURSOR

    (unknown) — 2002-11-18T02:27:41Z

    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...

  12. Re: [GENERAL] DECLARE CURSOR

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-18T04:39:41Z

    Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Let's just fix it and roll an RC2 with the fix.  If not, we can just fix
> > it in 7.3.1 but I see little problem in rolling an RC2.
> 
> Since Marc hasn't yet announced RC1, I think we could get away with just
> a quick fix and re-roll of RC1 ...

Once Marc puts it on FTP:

	-rw-r--r--  1 70  70   1073151 Nov 16 20:01 postgresql-test-7.3rc1.tar.gz

I think he likes to create a new release to avoid confusion.

Stamping RC2 now.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  13. Re: [GENERAL] DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-18T13:08:12Z

    Hello,
  is it planed cursor out of a transaction in 7.4 ?
Thanks
Haris Peco
On Monday 18 November 2002 12:30 am, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Let's just fix it and roll an RC2 with the fix.  If not, we can just fix
> > it in 7.3.1 but I see little problem in rolling an RC2.
>
> Here is the patch I am testing (in current sources; I don't think it
> needs any adjustments for REL7_3, but haven't tried to apply it yet).
> Basically it moves the test that was originally done in parse/analyze.c
> into the execution-time setup of a cursor, and enlarges the test to
> understand about autocommit-off and inside-a-function exceptions.
> Anyone see a problem?
>
> 			regards, tom lane
>
> *** src/backend/access/transam/xact.c.orig	Wed Nov 13 10:51:46 2002
> --- src/backend/access/transam/xact.c	Sun Nov 17 19:10:20 2002
> ***************
> *** 1488,1493 ****
> --- 1488,1537 ----
>   	}
>   }
>
> + /* --------------------------------
> +  *	RequireTransactionChain
> +  *
> +  *	This routine is to be called by statements that must run inside
> +  *	a transaction block, because they have no effects that persist past
> +  *	transaction end (and so calling them outside a transaction block
> +  *	is presumably an error).  DECLARE CURSOR is an example.
> +  *
> +  *	If we appear to be running inside a user-defined function, we do not
> +  *	issue an error, since the function could issue more commands that make
> +  *	use of the current statement's results.  Thus this is an inverse for
> +  *	PreventTransactionChain.
> +  *
> +  *	stmtNode: pointer to parameter block for statement; this is used in
> +  *	a very klugy way to determine whether we are inside a function.
> +  *	stmtType: statement type name for error messages.
> +  * --------------------------------
> +  */
> + void
> + RequireTransactionChain(void *stmtNode, const char *stmtType)
> + {
> + 	/*
> + 	 * xact block already started?
> + 	 */
> + 	if (IsTransactionBlock())
> + 		return;
> + 	/*
> + 	 * Are we inside a function call?  If the statement's parameter block
> + 	 * was allocated in QueryContext, assume it is an interactive command.
> + 	 * Otherwise assume it is coming from a function.
> + 	 */
> + 	if (!MemoryContextContains(QueryContext, stmtNode))
> + 		return;
> + 	/*
> + 	 * If we are in autocommit-off mode then it's okay, because this
> + 	 * statement will itself start a transaction block.
> + 	 */
> + 	if (!autocommit && !suppressChain)
> + 		return;
> + 	/* translator: %s represents an SQL statement name */
> + 	elog(ERROR, "%s may only be used in begin/end transaction blocks",
> + 		 stmtType);
> + }
> +
>
>   /* ----------------------------------------------------------------
>    *					   transaction block support
> *** /home/postgres/pgsql/src/backend/tcop/pquery.c.orig	Wed Sep  4 17:30:43
> 2002 --- /home/postgres/pgsql/src/backend/tcop/pquery.c	Sun Nov 17 19:10:26
> 2002 ***************
> *** 161,166 ****
> --- 161,168 ----
>   			/* If binary portal, switch to alternate output format */
>   			if (dest == Remote && parsetree->isBinary)
>   				dest = RemoteInternal;
> + 			/* Check for invalid context (must be in transaction block) */
> + 			RequireTransactionChain((void *) parsetree, "DECLARE CURSOR");
>   		}
>   		else if (parsetree->into != NULL)
>   		{
> *** /home/postgres/pgsql/src/include/access/xact.h.orig	Wed Nov 13 10:52:07
> 2002 --- /home/postgres/pgsql/src/include/access/xact.h	Sun Nov 17 19:10:13
> 2002 ***************
> *** 115,120 ****
> --- 115,121 ----
>   extern void UserAbortTransactionBlock(void);
>   extern void AbortOutOfAnyTransaction(void);
>   extern void PreventTransactionChain(void *stmtNode, const char
> *stmtType); + extern void RequireTransactionChain(void *stmtNode, const
> char *stmtType);
>
>   extern void RecordTransactionCommit(void);
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

  14. Re: DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-18T14:31:34Z

    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

  15. Re: [GENERAL] DECLARE CURSOR

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-18T14:38:59Z

    snpe <snpe@snpe.co.yu> writes:
>   is it planed cursor out of a transaction in 7.4 ?

I do not think we will allow cross-transaction cursors ever.  What would
it mean to have a cross-transaction cursor, anyway?  Does it show a
frozen snapshot as of the time it was opened?  The usefulness of that
seems awfully low in comparison to the pain of implementing it.

			regards, tom lane

  16. Re: [GENERAL] DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-18T14:46:29Z

    On Monday 18 November 2002 02:38 pm, Tom Lane wrote:
> snpe <snpe@snpe.co.yu> writes:
> >   is it planed cursor out of a transaction in 7.4 ?
>
> I do not think we will allow cross-transaction cursors ever.  What would
> it mean to have a cross-transaction cursor, anyway?  Does it show a
> frozen snapshot as of the time it was opened?  The usefulness of that
> seems awfully low in comparison to the pain of implementing it.
>
> 			regards, tom lane
It is in TODO list. Can You implement this with savepoint ?

regards
Haris Peco

  17. Re: [GENERAL] DECLARE CURSOR

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-18T15:45:15Z

    Haris Peco wrote:
> On Monday 18 November 2002 02:38 pm, Tom Lane wrote:
> > snpe <snpe@snpe.co.yu> writes:
> > >   is it planed cursor out of a transaction in 7.4 ?
> >
> > I do not think we will allow cross-transaction cursors ever.  What would
> > it mean to have a cross-transaction cursor, anyway?  Does it show a
> > frozen snapshot as of the time it was opened?  The usefulness of that
> > seems awfully low in comparison to the pain of implementing it.
> >
> > 			regards, tom lane
> It is in TODO list. Can You implement this with savepoint ?

I am planning on doing savepoints for 7.4.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  18. Re: DECLARE CURSOR

    scott.marlowe <scott.marlowe@ihs.com> — 2002-11-18T17:13:55Z

    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.

  19. Re: DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-18T17:32:36Z

    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

  20. Re: [GENERAL] DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-18T17:36:00Z

    On Monday 18 November 2002 03:45 pm, Bruce Momjian wrote:
> Haris Peco wrote:
> > On Monday 18 November 2002 02:38 pm, Tom Lane wrote:
> > > snpe <snpe@snpe.co.yu> writes:
> > > >   is it planed cursor out of a transaction in 7.4 ?
> > >
> > > I do not think we will allow cross-transaction cursors ever.  What
> > > would it mean to have a cross-transaction cursor, anyway?  Does it show
> > > a frozen snapshot as of the time it was opened?  The usefulness of that
> > > seems awfully low in comparison to the pain of implementing it.
> > >
> > > 			regards, tom lane
> >
> > It is in TODO list. Can You implement this with savepoint ?
>
> I am planning on doing savepoints for 7.4.

great.
Is it possible with savepoints next :
when am I in transaction and any command is error - only this command
is lost and I continue normal ?

Thanks
Haris Peco

  21. Re: [GENERAL] DECLARE CURSOR

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-18T17:38:51Z

    Haris Peco wrote:
> On Monday 18 November 2002 03:45 pm, Bruce Momjian wrote:
> > Haris Peco wrote:
> > > On Monday 18 November 2002 02:38 pm, Tom Lane wrote:
> > > > snpe <snpe@snpe.co.yu> writes:
> > > > >   is it planed cursor out of a transaction in 7.4 ?
> > > >
> > > > I do not think we will allow cross-transaction cursors ever.  What
> > > > would it mean to have a cross-transaction cursor, anyway?  Does it show
> > > > a frozen snapshot as of the time it was opened?  The usefulness of that
> > > > seems awfully low in comparison to the pain of implementing it.
> > > >
> > > > 			regards, tom lane
> > >
> > > It is in TODO list. Can You implement this with savepoint ?
> >
> > I am planning on doing savepoints for 7.4.
> 
> great.
> Is it possible with savepoints next :
> when am I in transaction and any command is error - only this command
> is lost and I continue normal ?

Yes, that will be part of it.  I am working on my proposal today.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  22. Re: [GENERAL] DECLARE CURSOR

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-18T17:46:22Z

    Haris Peco wrote:
> > > great.
> > > Is it possible with savepoints next :
> > > when am I in transaction and any command is error - only this command
> > > is lost and I continue normal ?
> >
> > Yes, that will be part of it.  I am working on my proposal today.
> Fine.What about cursor out of a transaction ?

That is not part of my work.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  23. Re: [GENERAL] DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-18T17:47:16Z

    On Monday 18 November 2002 05:38 pm, Bruce Momjian wrote:
> Haris Peco wrote:
> > On Monday 18 November 2002 03:45 pm, Bruce Momjian wrote:
> > > Haris Peco wrote:
> > > > On Monday 18 November 2002 02:38 pm, Tom Lane wrote:
> > > > > snpe <snpe@snpe.co.yu> writes:
> > > > > >   is it planed cursor out of a transaction in 7.4 ?
> > > > >
> > > > > I do not think we will allow cross-transaction cursors ever.  What
> > > > > would it mean to have a cross-transaction cursor, anyway?  Does it
> > > > > show a frozen snapshot as of the time it was opened?  The
> > > > > usefulness of that seems awfully low in comparison to the pain of
> > > > > implementing it.
> > > > >
> > > > > 			regards, tom lane
> > > >
> > > > It is in TODO list. Can You implement this with savepoint ?
> > >
> > > I am planning on doing savepoints for 7.4.
> >
> > great.
> > Is it possible with savepoints next :
> > when am I in transaction and any command is error - only this command
> > is lost and I continue normal ?
>
> Yes, that will be part of it.  I am working on my proposal today.
Fine.What about cursor out of a transaction ?
Thanks 
Haris Peco

  24. Re: [GENERAL] DECLARE CURSOR

    snpe <snpe@snpe.co.yu> — 2002-11-18T17:57:46Z

    On Monday 18 November 2002 05:46 pm, Bruce Momjian wrote:
> Haris Peco wrote:
> > > > great.
> > > > Is it possible with savepoints next :
> > > > when am I in transaction and any command is error - only this command
> > > > is lost and I continue normal ?
> > >
> > > Yes, that will be part of it.  I am working on my proposal today.
> >
> > Fine.What about cursor out of a transaction ?
>
> That is not part of my work.

Is it planned UNDO (WAL) ?

Thanks
Haris Peco

  25. Re: [GENERAL] DECLARE CURSOR

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-18T19:23:01Z

    Haris Peco wrote:
> On Monday 18 November 2002 05:46 pm, Bruce Momjian wrote:
> > Haris Peco wrote:
> > > > > great.
> > > > > Is it possible with savepoints next :
> > > > > when am I in transaction and any command is error - only this command
> > > > > is lost and I continue normal ?
> > > >
> > > > Yes, that will be part of it.  I am working on my proposal today.
> > >
> > > Fine.What about cursor out of a transaction ?
> >
> > That is not part of my work.
> 
> Is it planned UNDO (WAL) ?

No, see TODO.detail/transactions for info, or wait for my posting later
today.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  26. Re: DECLARE CURSOR

    (unknown) — 2002-11-19T18:20:28Z

    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...