Thread: some question about SavePoint ?

some question about SavePoint ?

From

zhaoxin

Date:

19 January 2006, 02:45:10

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.

Re: some question about SavePoint ?

From

Ludek Finstrle

Date:

19 January 2006, 10:20:39

> 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

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

From

Marc Herbert

Date:

19 January 2006, 13:00:20

Ludek Finstrle <luf@pzkagis.cz> 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.

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

From

Ludek Finstrle

Date:

19 January 2006, 13:24:28

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

Re: some question about SavePoint ?

From

zhaoxin

Date:

20 January 2006, 00:47:41

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
>

Re: some question about SavePoint ?

From

Ludek Finstrle

Date:

20 January 2006, 10:17:12

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

patch: BEGIN and autocommit(false)

From

Ludek Finstrle

Date:

20 January 2006, 12:23:47

Thu, Jan 19, 2006 at 03:03:54PM +0100, Marc Herbert wrote:
> Ludek Finstrle <luf@pzkagis.cz> 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

Attachment

psqlodbc-autocommit_off_begin.diff

Re: some question about SavePoint ?

From

zhaoxin

Date:

23 January 2006, 02:27:59


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 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
You mean I can use autocommit = 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.
>
My situation is like this , can you help me :
  1.set autocommit = off ;
  2.for(i=0;i<len;i++){
      insert into test(c1,c2) values(i,'a');
      if dupkey error occur then
      update test set c2 = 'b';--error:rollback first???
      else {
         rollback and return;
      }
   }
  3.commit;

I want to do like this:
  1. set autocommit = off ;
  2. for(i=0;i<len;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 = 'b';
      else{
         rollback and return;
      }
   }
   3.commit;

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

Regards,
zhao


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

--


以上、よろしくお願いいたします。

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

Re: some question about SavePoint ?

From

Ludek Finstrle

Date:

23 January 2006, 08:02:19

> I want to do like this:
>  1. set autocommit = off ;
>  2. for(i=0;i<len;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<len;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 <min> and <max> and c2 = 'a';
3. if (<update count> < (<max> - <min>)) then
     rollback;
   else
     commit;

<min> = 0 from your example
<max> = len from your example
<update count> = number of updated rows

Next way could be:
1. set autocommit = off;
2. select c1,c2 from test where c1 between <min> and <max> ...
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