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

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=10004 98&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-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=10004 98&group_id=1000125

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

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

--bp/iNruPH9dso1Pn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Fri, Dec 23, 2005 at 09:35:49AM +0000, noreply@pgfoundry.org napsal(a):
> Bugs item #1000498, was opened at 2005-12-21 15:19
>
> Why does an error breaks the savepoint environment ?

I fix this problem this way:
abort after query failed is examined only if there is autocommit on and
not running manual transaction.

Patch is attached. It could be applied only after psqlodbc-sqlstate.diff.

Please review and comment

Luf

--bp/iNruPH9dso1Pn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="psqlodbc-transact_abort.diff"

diff -c psqlodbc.orig\connection.c psqlodbc\connection.c
*** psqlodbc.orig\connection.c Sat Dec 24 11:29:39 2005
--- psqlodbc\connection.c Sat Dec 24 11:13:31 2005
***************
*** 1506,1521 ****
{
if ((res->status == PGRES_EMPTY_QUERY) || (res->status == PGRES_BAD_RESPONSE))
{
! mylog("send_query: sending query failed -> abort\n");
! QR_set_aborted(res, TRUE);
QR_Destructor(res);
res = NULL;
goto cleanup;
}
else if (res->status == PGRES_FATAL_ERROR)
{
! mylog("send_query: sended query failed -> abort\n");
! QR_set_aborted(res, TRUE);
goto cleanup;
}
if (create_keyset)
--- 1506,1527 ----
{
if ((res->status == PGRES_EMPTY_QUERY) || (res->status == PGRES_BAD_RESPONSE))
{
! if (CC_is_in_autocommit(self) && !CC_is_in_manual_trans(self))
! {
! mylog("send_query: sending query failed -> abort\n");
! QR_set_aborted(res, TRUE);
! }
QR_Destructor(res);
res = NULL;
goto cleanup;
}
else if (res->status == PGRES_FATAL_ERROR)
{
! if (CC_is_in_autocommit(self) && !CC_is_in_manual_trans(self))
! {
! mylog("send_query: sended query failed -> abort\n");
! QR_set_aborted(res, TRUE);
! }
goto cleanup;
}
if (create_keyset)

--bp/iNruPH9dso1Pn
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--bp/iNruPH9dso1Pn--

Re: patch for [ psqlodbc-Bugs-1000498 ] rollback to savepoint in case of error

--2fHTh5uZTiUOsy+g
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

> > Bugs item #1000498, was opened at 2005-12-21 15:19
> >
> > Why does an error breaks the savepoint environment ?
>
> I fix this problem this way:
> abort after query failed is examined only if there is autocommit on and
> not running manual transaction.
>
> Patch is attached. It could be applied only after psqlodbc-sqlstate.diff.
>
> Please review and comment
>
> Luf

I make previous patch againist older source :-(
I attach new one (it differ only in layout).

Regards,

Luf

--2fHTh5uZTiUOsy+g
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="psqlodbc-transact_abort.diff"

diff -c psqlodbc.orig\connection.c psqlodbc\connection.c
*** psqlodbc.orig\connection.c Sat Dec 24 11:44:33 2005
--- psqlodbc\connection.c Sat Dec 24 11:53:18 2005
***************
*** 1506,1521 ****
{
if ((res->status == PGRES_EMPTY_QUERY) || (res->status == PGRES_BAD_RESPONSE))
{
! mylog("send_query: sending query failed -> abort\n");
! QR_set_aborted(res, TRUE);
QR_Destructor(res);
res = NULL;
goto cleanup;
}
else if (res->status == PGRES_FATAL_ERROR)
{
! mylog("send_query: sended query failed -> abort\n");
! QR_set_aborted(res, TRUE);
goto cleanup;
}
if (create_keyset)
--- 1506,1527 ----
{
if ((res->status == PGRES_EMPTY_QUERY) || (res->status == PGRES_BAD_RESPONSE))
{
! if (CC_is_in_autocommit(self) && !CC_is_in_manual_trans(self))
! {
! mylog("send_query: sending query failed -> abort\n");
! QR_set_aborted(res, TRUE);
! }
QR_Destructor(res);
res = NULL;
goto cleanup;
}
else if (res->status == PGRES_FATAL_ERROR)
{
! if (CC_is_in_autocommit(self) && !CC_is_in_manual_trans(self))
! {
! mylog("send_query: sended query failed -> abort\n");
! QR_set_aborted(res, TRUE);
! }
goto cleanup;
}
if (create_keyset)

--2fHTh5uZTiUOsy+g
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--2fHTh5uZTiUOsy+g--