Thread: Re: [GENERAL] DECLARE CURSOR
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 usingIsTransactionBlock() as an (inadequate) check
thatthey'd get tocommit immediately on finishing. There's now a centralized routinePreventTransactionChain() that
implementsthe 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, Pennsylvania19073
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
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);
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, Pennsylvania19073
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
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, Pennsylvania19073
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, Pennsylvania19073
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
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, Pennsylvania19073
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, Pennsylvania19073