Why would Vertica let me start a transaction if I can't roll it back?

Question

I run the following statements against a Vertica database, one at a time:

BEGIN TRANSACTION;

UPDATE table
SET col1 = 'something'
WHERE col2 = 'something else';

SELECT COUNT(*)
FROM table
WHERE col1 = 'something';

ROLLBACK TRANSACTION;

I run the first line fine... OK, now I'm in a transaction.

I run my update... OK, that worked.

I run my SELECT test to make sure things worked as expected... Oh wait, it looks like I missed a condition in the WHERE clause of my UPDATE statement.

No worries! That's why I did this in a transaction.

Let's rollback:

=> ROLLBACK TRANSACTION;

[Vertica][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.

Jones, hand over that roll of toilet paper you have on your desk.

So Vertica happily accepted my BEGIN TRANSACTION, knowing full well that very soon after that I would try to run either a ROLLBACK or COMMIT.

Yet, I can't! My connection is in auto-commit mode, so ROLLBACK and COMMIT mean nothing. My UPDATE was committed the moment it completed.

Did I miss something, or am I right in thinking this is just a very bad implementation on Vertica's part?

Why would Vertica accept a BEGIN TRANSACTION on a connection in auto-commit mode if the logical consequences (ROLLBACK or COMMIT) are illegal?

Answer 1

Vertica (actually, I think most if not all databases) behaves in this manner because the part that processes the BEGIN TRANSACTION SQL statement, namely the query parsing and execution engine, is not aware of the client AUTOCOMMIT setting. On the other hand, the client is not aware of the meaning of the string 'BEGIN TRANSACTION'.

The flow of control looks something like this, assuming the command line client vsql is used to run the example:

1. User starts vsql and connects to the database.
2. User enters \set AUTOCOMMIT on. After that moment the client (vsql) knows to issue an implicit COMMIT statement after each successful statement.
3. User enters BEGIN TRANSACTION. vsql does not recognize that string as one of its internal commands and sends the string to the server for processing.
4. Server recognizes the string as a valid SQL statement, compiles it, starts a transaction, and returns a successful result code to the client. Since autocommit is a client-side setting, the server has no idea about it.
5. Client, upon receiving the successful result code, issues COMMIT to the server.
6. Server complies and commits the transaction it has just started.

etc.

Answer 2

Vertica support have acknowledged this issue and are tracking a fix under VER-44735. The Vertica issue tracker is not publicly visible, unfortunately.

Vertica refused to characterize this as a bug and labeled it instead as a "new feature request", but regardless this should be addressed in an upcoming release.