Reputation
8,720
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
10 53 90
Impact
~526k people reached

Mar
29
accepted Why would Vertica let me start a transaction if I can't roll it back?
Mar
29
revised Why would Vertica let me start a transaction if I can't roll it back?
clarify that the error I got was after trying to ROLLBACK
Mar
29
comment Why would Vertica let me start a transaction if I can't roll it back?
@a_horse_with_no_name - My objection here was that Vertica accepted my BEGIN TRANSACTION but then rejected my ROLLBACK because I was in auto-commit mode. So clearly the component issuing the rejection (i.e. the JDBC driver? It's not DBeaver though) had enough information to either also reject the BEGIN TRANSACTION or to temporarily disable auto-commit. Neither happened, which is dangerous from a UX perspective. The workaround is clear (disable auto-commit), but I wanted to understand whether what I was seeing was a real problem or just a misunderstanding. I believe it's the former.
Mar
29
comment Why would Vertica let me start a transaction if I can't roll it back?
@a_horse_with_no_name - Oh, is that how it works? Would it be the client or the JDBC driver that does the disabling? The error rejecting the ROLLBACK seemed to be coming from the JDBC driver, so it seems like that same component should also be rejecting the BEGIN (or disabling auto-commit, of course). And I believe Vertica support conceded this point. So in this case, I believe DBeaver wouldn't be at fault since the JDBC driver is provided by Vertica. Does that make sense?
Mar
29
comment Why would Vertica let me start a transaction if I can't roll it back?
@a_horse_with_no_name - I'm using DBeaver. The issue is with Vertica and not the client, in this case.
Mar
29
answered Why would Vertica let me start a transaction if I can't roll it back?
Mar
28
accepted How do I get the size of a Vertica database?
Mar
25
accepted Why does CURRENT_LOAD_SOURCE() return “Batch No. 1” instead of the actual source file name?
Mar
24
revised Why does CURRENT_LOAD_SOURCE() return “Batch No. 1” instead of the actual source file name?
added 173 characters in body
Mar
24
revised Why does CURRENT_LOAD_SOURCE() return “Batch No. 1” instead of the actual source file name?
clarify actual vs. intended behavior
Mar
23
asked Why does CURRENT_LOAD_SOURCE() return “Batch No. 1” instead of the actual source file name?
Mar
23
answered Why does CURRENT_LOAD_SOURCE() return “Batch No. 1” instead of the actual source file name?
Mar
14
comment Set default database for connection
And in some cases you may need to add an END TRANSACTION immediately after that before you can run other statements.
Mar
14
accepted Set default database for connection
Mar
14
comment Set default database for connection
Aha, it looks like ET from that error message means END TRANSACTION. Doing that right after the DATABASE ... statement makes subsequent queries work. @Phil - Wanna submit your comment as an answer, or should I do the honors?
Mar
14
comment Set default database for connection
@Phil - That should be it, but when I try to run a simple SELECT after that I get this: Only an ET or null statement is legal after a DDL Statement. It looks like we need to do something immediately after the DATABASE ... statement.
Mar
10
asked Set default database for connection
Mar
9
comment Why would Vertica let me start a transaction if I can't roll it back?
Assuming we've understood the behavior correctly, I would count this as a clear case of poor design or user-unfriendliness and not a matter of opinion. The contrast with SQL Server's behavior, plus the fact that Vertica clearly knows the connection is in auto-commit mode but interprets the BEGIN TRANSACTION statement in such a pathological way, are proof enough of this. I'll ask Vertica's support about this to see if they agree. :)
Mar
8
comment Why would Vertica let me start a transaction if I can't roll it back?
The error message is: [Vertica][JDBC](10040) Cannot use commit while Connection is in auto-commit mode. What is generating that message? I'm guessing it's either the server or some part of the JDBC driver, most likely the latter. Both of those pieces are written by Vertica. Since the component generating the error message is clearly aware that the connection is in auto-commit mode, shouldn't it be capable of raising a similar error when the client issues a BEGIN TRANSACTION?
Mar
8
awarded  Great Answer