DECLARE CURSOR

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

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

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

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

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);

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

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

Hello,
is it planed cursor out of a transaction in 7.4 ?
Thanks
Haris Peco

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

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

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

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

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.

It is usually used with comitted read isolation for an outer select
on one table and one transaction per row where the action usually involving
additional tables depends on the selected row. This is to keep transactions
small and avoid locking out other activity.

The outer cursor is declared "WITH HOLD".
I think it is a useful feature.

Of course a workaround is to open two connections.

Andreas

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

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

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

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

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

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

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