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

Ludek Finstrle <luf(at)pzkagis(dot)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.