Skip site navigation (1) Skip section navigation (2)

[ psqlodbc-Bugs-1000498 ] rollback to savepoint in case of error

From: <noreply(at)pgfoundry(dot)org>
To: noreply(at)pgfoundry(dot)org
Subject: [ psqlodbc-Bugs-1000498 ] rollback to savepoint in case of error
Date: 2005-12-24 10:40:45
Message-ID: 20051224104045.8B3711125078@pgfoundry.org (view raw or flat)
Thread:
Lists: pgsql-odbc
Bugs item #1000498, was opened at 2005-12-21 15:19
You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000498&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: Accepted
Priority: 3
Submitted By: Tom Zschockelt (toz)
Assigned to: Ludek Finstrle (luf)
Summary: rollback to savepoint in case of error

Initial Comment:
the current snapshot driver only supports manual rollback to savepoints
if ( and only if ) all previous statements return with success.

e.g.

My env :

win2003 server
8.1.0 postgresql
psqlodbc 8.01.0105


CREATE TABLE testtable (
f1 int2 primary key,
f2 varchar(10) null);


the following snippet works

BEGIN
INSERT INTO testtable values ( 12, 'hello');
SAVEPOINT SP_INSERT;
INSERT INTO testtable values ( 13, 'hello1');
ROLLBACK TO SAVEPOINT SP_INSERT;
INSERT INTO testtable values ( 13, 'hello123');
COMMIT;

the following snippet don't work

BEGIN
INSERT INTO testtable values ( 12, 'hello');
SAVEPOINT SP_INSERT;
INSERT INTO testtable values ( 12, 'hello1'); <-- auto rollback occurs !!!
ROLLBACK TO SAVEPOINT SP_INSERT;
INSERT INTO testtable values ( 13, 'hello123');
COMMIT;

Why does an error breaks the savepoint environment ?

By now I used an application side workaround as I do an select before 
each insert to check if a certain row already exists in the table.
If it exist I try an update on the row.
( but this is a real performance killer ... )

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

>Comment By: Ludek Finstrle (luf)
Date: 2005-12-24 11:40

Message:
Please try new development snapshot 8.01.0106. The problem may be fixed there.

I attach patch. It have to be applied after psqlodbc-sqlstate.diff.

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

Comment By: Ludek Finstrle (luf)
Date: 2005-12-23 10:35

Message:
I accept the bug in case autocommit = off => don't take a care on transaction state.

What driver version do you use? Do you use 8.01.0105 patched with SqlState patch?

Could you attach mylog output for second example (the failure one)?

Thanks

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

Comment By: Tom Zschockelt (toz)
Date: 2005-12-22 20:17

Message:
I switched autocommit off with the  
SQLSetConnectAttr() Function 
 
See the first example which works well !!! 
 
 

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

Comment By: Ludek Finstrle (luf)
Date: 2005-12-22 19:57

Message:
What about autocommit StmtOption? Don't you have autocommit on?
Driver doesn't support savepoints in this case yet.

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

You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000498&group_id=1000125

pgsql-odbc by date

Next:From: Ludek FinstrleDate: 2005-12-24 11:06:08
Subject: psqlODBC 8.01.0106 Development snapshot
Previous:From: Ludek FinstrleDate: 2005-12-24 09:54:04
Subject: Re: patch for [ psqlodbc-Bugs-1000498 ] rollback to savepoint in case of error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group