some question about SavePoint ?

Hi,

I have a question about psqlodbc .

After I execute a SQL , I want to ignore some error ,and continue to
execute my SQL.but I will get some error like that :

"current transaction is aborted, commands ignored until end of
transaction block"

I try to use SavePoint to solve this trouble , but I got some problem .
example:
.....
1. set SQL_AUTOCOMMIT_OFF
2. SQLConnect()
..............
3. SQLExecDirect() -> SavePoint sp ;
4. SQLExecDirect() -> insert into test values(...) ;
5. SQLExecDirect() -> Release SavePoint sp;
6. SQLEndTran() -> commit ;
7. SQLExecDirect() -> SavePoint sp ;
^^^^^^^^^^^^^^^^
8. SQLExecDirect() -> update test;
.............

Until 6, it return OK, but at 7 , it will return some error like

"SAVEPOINT may only be used in transaction blocks"

so , I have to use "begin" to start another transaction after I execute
the "commit" at 6 ?
Why can I use savepoint at 3 without execute any "begin" ???
Or I have some mistake ?
My psqlodbc version is 8.01.01.02(Unicode)

And, I have to use SavePoint to rollback to current SQL?
Don't I have another way in psqlodbc ?


Regards, Zhao.



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: some question about SavePoint ?

> After I execute a SQL , I want to ignore some error ,and continue to
> execute my SQL.but I will get some error like that :
>
> "current transaction is aborted, commands ignored until end of
> transaction block"

There is some common known bugs in 08.01.0102 in implicit rollback.
Do you try latest development snapshot?
Maybe 08.01.0106 could better fit you. I don't know. We have some report
about similar problem (but reporter doesn't mentioned his psqlodbc version).

> I try to use SavePoint to solve this trouble , but I got some problem .
> example:
> .....
> 1. set SQL_AUTOCOMMIT_OFF

In this case you may call BEGIN (START TRANSACTION is supported since
08.01.0107).

> 2. SQLConnect()
> ..............
> 3. SQLExecDirect() -> SavePoint sp ;
> 4. SQLExecDirect() -> insert into test values(...) ;
> 5. SQLExecDirect() -> Release SavePoint sp;
> 6. SQLEndTran() -> commit ;
> 7. SQLExecDirect() -> SavePoint sp ;
> ^^^^^^^^^^^^^^^^
> 8. SQLExecDirect() -> update test;
> .............
>
> Until 6, it return OK, but at 7 , it will return some error like
>
> "SAVEPOINT may only be used in transaction blocks"
>
> so , I have to use "begin" to start another transaction after I execute
> the "commit" at 6 ?
> Why can I use savepoint at 3 without execute any "begin" ???
> Or I have some mistake ?

Yes you do ... When you use autocommit = off you have to start and end
transaction manually. There is hack in psqlodbc which start transaction
automatically even in autocommit = off when you use select, insert, update
or delete SQL statement.

> My psqlodbc version is 8.01.01.02(Unicode)
>
> And, I have to use SavePoint to rollback to current SQL?
> Don't I have another way in psqlodbc ?

What about try the latest development snapshot as I mentioned above?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

BEGIN and autocommit(false) (was: some question about SavePoint ?)

Ludek Finstrle writes:

>> 1. set SQL_AUTOCOMMIT_OFF
>
> Yes you do ... When you use autocommit = off you have to start and end
> transaction manually.

Wrong: in ODBC, you just have to "end" transactions manually, you
don't have to start them, they are implicitely started when the
previous one ends.

In ODBC (and JDBC), you are ALWAYS inside a transaction, there is no
such thing as a transactional "no man's land" from an ODBC interface
point of view (internal, hidden implementation details may differ).


From the microsoft ODBC spec:

Transactions in ODBC do not have to be explicitly initiated. Instead,
a transaction begins implicitly whenever the application starts
operating on the database. If the data source requires explicit
transaction initiation, the driver must provide it whenever the
application executes a statement requiring a transaction and there is
no current transaction.

The first half describes the ODBC interface, and the second a possible
implementation.


> There is hack in psqlodbc which start transaction
> automatically even in autocommit = off when you use select, insert, update
> or delete SQL statement.

I don't find this a "hack". The whole old "BEGIN" concept becomes
irrelevant once you have an interface with this new concept of
"manual/auto-commit" mode. It's only when you wrongly mix the old
BEGIN and new AUTOCOMMIT concepts in the same interface that things
become confusing.

When you are using an interface with the new "mode" concept and just
committed a transaction in manual-commit mode, then what else could
you do besides a BEGIN ?! "BEGIN" simply makes no sense in this new
mode-based interface style.

The ODBC and JDBC standards have made their choice: they opted for the
manual/auto-commit mode new concept, and _removed_ the old concept of
BEGIN: there simply is no "BEGIN" in these new style interfaces.

Of course you can quite trivially implement one interface style _on
top of_ the other, if ever needed. That is probably what pgodbc
does/should do. That does not imply mixing the two concepts. Please.




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: BEGIN and autocommit(false) (was: some question about SavePoint ?)

> >> 1. set SQL_AUTOCOMMIT_OFF
> >
> > Yes you do ... When you use autocommit = off you have to start and end
> > transaction manually.
>
> Wrong: in ODBC, you just have to "end" transactions manually, you
> don't have to start them, they are implicitely started when the
> previous one ends.
>
> In ODBC (and JDBC), you are ALWAYS inside a transaction, there is no
> such thing as a transactional "no man's land" from an ODBC interface
> point of view (internal, hidden implementation details may differ).

Thanks a lot for showing me the rigth way. I was confused when
I touch the relevant code. It's clear for me now.

> Of course you can quite trivially implement one interface style _on
> top of_ the other, if ever needed. That is probably what pgodbc
> does/should do. That does not imply mixing the two concepts. Please.

Ok. I'll take a look at the code with this (for me) new informations.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: some question about SavePoint ?

First of all , Thanks for you response.
>>After I execute a SQL , I want to ignore some error ,and continue to
>>execute my SQL.but I will get some error like that :
>>
>>"current transaction is aborted, commands ignored until end of
>>transaction block"
>
>
> There is some common known bugs in 08.01.0102 in implicit rollback.
> Do you try latest development snapshot?
> Maybe 08.01.0106 could better fit you. I don't know. We have some report
> about similar problem (but reporter doesn't mentioned his psqlodbc version).
>
>
Do you mean that in 08.01.0106 I must not use the Savepoint to ignore
some error ?? When error occurred in 08.01.0106, It can be continued to
execute SQL without executing rollback first ???
What I want is to ignore some error returned ,and coninue to execute my
SQL , do I have another way ?

Regards,
zhao

>>I try to use SavePoint to solve this trouble , but I got some problem .
>>example:
>> .....
>> 1. set SQL_AUTOCOMMIT_OFF
>
>
> In this case you may call BEGIN (START TRANSACTION is supported since
> 08.01.0107).
>
>
>> 2. SQLConnect()
>> ..............
>> 3. SQLExecDirect() -> SavePoint sp ;
>> 4. SQLExecDirect() -> insert into test values(...) ;
>> 5. SQLExecDirect() -> Release SavePoint sp;
>> 6. SQLEndTran() -> commit ;
>> 7. SQLExecDirect() -> SavePoint sp ;
>> ^^^^^^^^^^^^^^^^
>> 8. SQLExecDirect() -> update test;
>> .............
>>
>>Until 6, it return OK, but at 7 , it will return some error like
>>
>> "SAVEPOINT may only be used in transaction blocks"
>>
>>so , I have to use "begin" to start another transaction after I execute
>>the "commit" at 6 ?
>>Why can I use savepoint at 3 without execute any "begin" ???
>>Or I have some mistake ?
>
>
> Yes you do ... When you use autocommit = off you have to start and end
> transaction manually. There is hack in psqlodbc which start transaction
> automatically even in autocommit = off when you use select, insert, update
> or delete SQL statement.
>
>
>>My psqlodbc version is 8.01.01.02(Unicode)
>>
>>And, I have to use SavePoint to rollback to current SQL?
>>Don't I have another way in psqlodbc ?
>
>
> What about try the latest development snapshot as I mentioned above?
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: some question about SavePoint ?

> >There is some common known bugs in 08.01.0102 in implicit rollback.
> >Do you try latest development snapshot?
> >Maybe 08.01.0106 could better fit you. I don't know. We have some report
> >about similar problem (but reporter doesn't mentioned his psqlodbc
> >version).
>
> Do you mean that in 08.01.0106 I must not use the Savepoint to ignore
> some error ?? When error occurred in 08.01.0106, It can be continued to
> execute SQL without executing rollback first ???

There is some changes with implicit rollback in autocommit = on. I don't
know your situation exactly so it should or shouldn't help you.
The answer is:
Maybe yes. You have to try.

> What I want is to ignore some error returned ,and coninue to execute my
> SQL , do I have another way ?

Yes, you have. You could send us your mylog output and we could take
a look at your problem. But you could be sure we don't study 08.01.0102
mylog output becouse there are changes in development snapshots.

The best way is try tha latest development snapshot (08.01.0107). We're
going to release new "stable" version based ont 08.01.0107 in relative
short time. So if you want have easier life with new stable release you
may try the 08.01.0107 and report problem if there is one.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

patch: BEGIN and autocommit(false)

--x+6KMIRAuhnl3hBn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Thu, Jan 19, 2006 at 03:03:54PM +0100, Marc Herbert wrote:
> Ludek Finstrle writes:
> >> 1. set SQL_AUTOCOMMIT_OFF
> >
> > Yes you do ... When you use autocommit = off you have to start and end
> > transaction manually.
>
> Wrong: in ODBC, you just have to "end" transactions manually, you
> don't have to start them, they are implicitely started when the
> previous one ends.
>
> In ODBC (and JDBC), you are ALWAYS inside a transaction, there is no
> such thing as a transactional "no man's land" from an ODBC interface
> point of view (internal, hidden implementation details may differ).

I hope I solve this issue in psqlodbc driver. Thank you Marc for pointing
me the right way.
Patch attached (it's created againist 08.01.0107 development snapshot,
not againist CVS). This patch depend on psqlodbc-implicit_rollback.diff
one.

Please review and comment

Luf

--x+6KMIRAuhnl3hBn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="psqlodbc-autocommit_off_begin.diff"

diff -c psqlodbc.orig\statement.c psqlodbc\statement.c
*** psqlodbc.orig\statement.c Fri Jan 20 14:09:35 2006
--- psqlodbc\statement.c Fri Jan 20 15:15:06 2006
***************
*** 71,76 ****
--- 71,79 ----
STMT_TYPE_LOCK, "LOCK"
},
{
+ STMT_TYPE_BEGIN, "BEGIN"
+ },
+ {
0, NULL
}
};
***************
*** 423,428 ****
--- 426,441 ----
if (!strnicmp(statement, Statement_Type[i].s, strlen(Statement_Type[i].s)))
return Statement_Type[i].type;

+ /* determine START TRANSACTION */
+ if (!strnicmp(statement, "START", 5))
+ {
+ statement += 5;
+ /* ignore whitespace in query string */
+ while (*statement && isspace((UCHAR) *statement))
+ statement++;
+ if (!strnicmp(statement, "TRANSACTION", 11))
+ return STMT_TYPE_BEGIN;
+ }
return STMT_TYPE_OTHER;
}

***************
*** 1148,1154 ****
QueryInfo qi;
ConnInfo *ci;
UDWORD qflag = 0;
! BOOL auto_begin = FALSE, is_in_trans;
int func_cs_count = 0;


--- 1161,1167 ----
QueryInfo qi;
ConnInfo *ci;
UDWORD qflag = 0;
! BOOL is_in_trans;
int func_cs_count = 0;


***************
*** 1175,1184 ****
is_in_trans = CC_is_in_trans(conn);
if (!self->internal && !is_in_trans &&
(SC_is_fetchcursor(self) ||
! (!CC_is_in_autocommit(conn) && self->statement_type != STMT_TYPE_OTHER)))
{
mylog(" about to begin a transaction on statement = %u\n", self);
- auto_begin = TRUE;
if (PG_VERSION_GE(conn, 7.1))
qflag |= GO_INTO_TRANSACTION;
else if (!CC_begin(conn))
--- 1188,1196 ----
is_in_trans = CC_is_in_trans(conn);
if (!self->internal && !is_in_trans &&
(SC_is_fetchcursor(self) ||
! (!CC_is_in_autocommit(conn) && self->statement_type != STMT_TYPE_BEGIN)))
{
mylog(" about to begin a transaction on statement = %u\n", self);
if (PG_VERSION_GE(conn, 7.1))
qflag |= GO_INTO_TRANSACTION;
else if (!CC_begin(conn))
***************
*** 1252,1258 ****
*/
if (CC_is_in_trans(conn))
{
! if (!is_in_trans && !auto_begin)
CC_set_in_manual_trans(conn);
if (!self->internal && CC_is_in_autocommit(conn) && !CC_is_in_manual_trans(conn))
CC_commit(conn);
--- 1264,1270 ----
*/
if (CC_is_in_trans(conn))
{
! if (!is_in_trans)
CC_set_in_manual_trans(conn);
if (!self->internal && CC_is_in_autocommit(conn) && !CC_is_in_manual_trans(conn))
CC_commit(conn);
diff -c psqlodbc.orig\statement.h psqlodbc\statement.h
*** psqlodbc.orig\statement.h Wed Nov 30 13:44:37 2005
--- psqlodbc\statement.h Fri Jan 20 15:07:21 2006
***************
*** 96,102 ****
STMT_TYPE_GRANT,
STMT_TYPE_REVOKE,
STMT_TYPE_PROCCALL,
! STMT_TYPE_LOCK
};

#define STMT_UPDATE(stmt) (stmt->statement_type > STMT_TYPE_SELECT)
--- 96,103 ----
STMT_TYPE_GRANT,
STMT_TYPE_REVOKE,
STMT_TYPE_PROCCALL,
! STMT_TYPE_LOCK,
! STMT_TYPE_BEGIN
};

#define STMT_UPDATE(stmt) (stmt->statement_type > STMT_TYPE_SELECT)

--x+6KMIRAuhnl3hBn
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--x+6KMIRAuhnl3hBn--

Re: some question about SavePoint ?

Ludek Finstrle wrote:
>>>There is some common known bugs in 08.01.0102 in implicit rollback.
>>>Do you try latest development snapshot?
>>>Maybe 08.01.0106 could better fit you. I don't know. We have some repo=
rt
>>>about similar problem (but reporter doesn't mentioned his psqlodbc=20
>>>version).
>>
>>Do you mean that in 08.01.0106 I must not use the Savepoint to ignore=20
>>some error ?? When error occurred in 08.01.0106, It can be continued to=
=20
>>execute SQL without executing rollback first ???
>=20
>=20
> There is some changes with implicit rollback in autocommit =3D on. I do=
n't
You mean I can use autocommit =3D on and begin a transaction manually???
Is the implicit rollback to rollback all before ???
> know your situation exactly so it should or shouldn't help you.
> The answer is:
> Maybe yes. You have to try.
>=20
My situation is like this , can you help me :
1.set autocommit =3D off ;
2.for(i=3D0;i insert into test(c1,c2) values(i,'a');
if dupkey error occur then
update test set c2 =3D 'b';--error:rollback first???
else {
rollback and return;
}
}
3.commit;

I want to do like this:
1. set autocommit =3D off ;
2. for(i=3D0;i savepoint sp;
insert into test(c1,c2) values(i,'a');
release savepoint sp;
if dupkey error occur then
rollback to sp;
update test set c2 =3D 'b';
else{
rollback and return;
}
}
3.commit;

Can I use another way to implenment this ? (don't use the savepoint)
thanks.

Regards,
zhao


>=20
>>What I want is to ignore some error returned ,and coninue to execute my=
=20
>>SQL , do I have another way ?
>=20
>=20
> Yes, you have. You could send us your mylog output and we could take
> a look at your problem. But you could be sure we don't study 08.01.0102
> mylog output becouse there are changes in development snapshots.
>=20
> The best way is try tha latest development snapshot (08.01.0107). We're
> going to release new "stable" version based ont 08.01.0107 in relative
> short time. So if you want have easier life with new stable release you
> may try the 08.01.0107 and report problem if there is one.
>=20
> Regards,
>=20
> Luf
>=20

--=20


以上、よろしだãŠ=E9=
¡˜ã„いたします ã€=82

---------------------------------------------------------
Zhao Xin
NEC-CAS Software Laboratories Co.,Ltd.
Tel : 8233-4433-425
Telnet : 8-0086-22-425
E-mail:ã€=80zhaox@necas.nec.com.cn
--------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: some question about SavePoint ?

> I want to do like this:
> 1. set autocommit = off ;
> 2. for(i=0;i > savepoint sp;
> insert into test(c1,c2) values(i,'a');
> release savepoint sp;
> if dupkey error occur then
> rollback to sp;

You can't rollback to released savepoint.

> update test set c2 = 'b';
> else{
> rollback and return;
> }
> }
> 3.commit;

This may work. But the performance is low.

1. set autocommit = off ;
2. for(i=0;i savepoint sp;
insert into test(c1,c2) values(i,'a');
if dupkey error occur then
rollback to sp;
release savepoint sp;
update test set c2 = 'b';
else{
rollback and return;
}
}
3.commit;

> Can I use another way to implenment this ? (don't use the savepoint)

If you use autocommit = off and test each line separately I see no
other way. It's the backend behaviour when something fail inside
manual transaction. You could try it in psql.

I suppose c1 or (ci, c2) is primary or unique key.
What about something like (I don't fully understand what you
really want):

1. set autocommit = off;
2. update test set c2='b' where c1 between and and c2 = 'a';
3. if ( < ( - )) then
rollback;
else
commit;

= 0 from your example
= len from your example
= number of updated rows

Next way could be:
1. set autocommit = off;
2. select c1,c2 from test where c1 between and ...
3. if (check the data) then
update or for(...) update
commit;
else
rollback;

I'm not sure if this really remain into pgsql-odbc mailing list. There
are maybe more suitable people in pgsql-sql or another pgsql-* mailing
list.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend