Re: rollback to savepoint support - Mailing list pgsql-odbc

Not much.
I planned that a bit and did some code, but I did not do it
so, that it could be accepted easilly to the source code.

A very limited savepoint support needs one fix:

connection.c: CC_send_query function fails:

else if (strnicmp(cmdbuffer, "ROLLBACK", 8) == 0)
                        CC_on_abort(self, NO_TRANS);

ROLLBACK TO savep_1; will do a full abort.
An easy fix is to do the following:

else if (strnicmp(cmdbuffer, "ROLLBACK", 8) == 0 &&
            strnicmp(cmdbuffer, "ROLLBACK TO", 11) != 0)
                        CC_on_abort(self, NO_TRANS);


So, "ROLLBACK TO <savepoint_name>" must be handled differently
than "ROLLBACK".

Harder part is the following:
ROLLBACK TO <savepoint_name>
might remove some cursors on the backend side without a notice.
Those cursors must not be CLOSEd by the ODBC driver.

I personally know only one way for the cursor maintenance:
"Keep track of all <savepoint_name>s. When a savepoint has been
rolled back, or a savepoint name released, maintain them on the ODBC side
as a linked list or something.

This enables the following things:
1. On ROLLBACK TO <savepoint_name> we can find out the CURSORS, that
are being removed by the BACKEND without notice. We then remove them
from the ODBC statement structures.
2. On RELEASE <savepoint_name> we can maintain the linked list of
savepoints and the CURSORS on the statements, that on which savepoint they
are defined.


I have not done a full implementation of this subject.
I have done some experiments though:

If I implement into ODBC SQLExec() API a parser, that catches the relevant
transaction, savepoint and cursor commands, the parser is able to handle
about 600 000 queries on a recent computer, per second, with the complete
PostgreSQL 8.x sentences for them, assuming UTF-8, without
ODBC bind support:

SQLPrepare("RELEASE ?");
SQLBind(savepoint_name,1);

An example of the statement, that the parser understand:

BEGIN [ WORK | TRANSACTION ] [/transaction_mode/ [, ...] ]

 where /transaction_mode/ is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY


So I used Flex and Bison for my experiment.

So my personal view of requirements for this task are:

- FULL PostgreSQL 8.x savepoint support.
This means, that the ODBC must fully support the SAVEPOINT definitions
documented in the PostgreSQL 8.x SQL reference.

- ODBC standards requirements: What they are ? There are some mentions
on the PostgreSQL 8.x SQL reference. So my personal ODBC compilance is best,
if I can comply with the PostgreSQL 8.x.

- ROBUST PostgreSQL 8.x savepoint support.
If the ODBC user adds two spaces between "ROLLBACK" and "TO" like
"ROLLBACK  TO",
it must work as there would be only one space. Thus I decided to use
Flex and Bison,
because with tools like them, it is easier to conform with such
requirements.

- FAST PostgreSQL 8.x savepoint support.
Flex and Bison can process 600 000 small and easy rows per second. Only
the relevant
rows must be processed fully.

There is also the existing parser for SQLPrepare/Bind/SQLExecute needs.
+ Works now.
+ Supports 8 and multibyte characters (chinese, japanese, ...)
-  Seems to be the best and robust available for PostgreSQL ODBC.
-  Hardly understandable for me.

So for a simple savepoint support, the following design could work?
- SQPrepare/Bind/SQLExecute parser: enlarge it to handle savepoints.
- connection structure: insert a linked list for the savepoint names.
- statement structure: Store the savepoint name for those cursors,
that might be closed during savepoint rollback.
- Implement lower level functions to support the savepoint functionality.

Personally I would like about rewriting the SQLPrepare/Bind/SQLExecute
parser,
because it would be easier to extend and make to support the full
PostgreSQL 8.x SQL language.
Then it would need flex and bison to be compilable on Windows. Not a big
minus.

I would also like to see the psqlODBC to use UTF8 internally on every
platform. So the backend would talk with psqlODBC only with UTF8.
Every unnecessary conversion means less speed though.

Regards,
Marko Ristola

Jonathan Fuerth wrote:

> Hi everyone,
>
> Has there been any work done on supporting rollback to savepoint in
> the ODBC driver?  The current driver issues a full rollback to the
> backend at the first sign of trouble, thereby eliminating the
> possibility of returning to a savepoint within the transaction.
>
> -Jonathan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend