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?
AUTO COMMIT
was disabled, then you would need aCOMMIT
after any DDL. – Kermit Mar 7 '16 at 19:05BEGIN TRANSACTION
in auto-commit mode when it is in fact being ignored, as I demonstrated. It's a matter of design, or user-friendliness, if you will. I think I was pretty clear about that. – Nick Chammas Mar 7 '16 at 19:17BEGIN TRANSACTION
, Vertica is falsely implying to the user that they are within a transactional scope and will be able to roll back their actions. What Vertica should do instead, I believe, is fail loudly on theBEGIN TRANSACTION
when the connection is in auto-commit mode to signal clearly that user-controlled transactions are not allowed. – Nick Chammas Mar 7 '16 at 19:20AUTOCOMMIT
is enabled. – Kermit Mar 7 '16 at 20:10