Dbeaver: Add configurable commit mode?

[zejji]

Currently DBeaver supports auto-commit and manual commit modes. Would it be possible to add a configurable commit mode which only starts a transaction when certain types of queries are executed (in particular I am thinking of data-modifying queries such as INSERT, REPLACE, UPDATE and DELETE, as well as schema changes). The user could have the option of specifying which commands should automatically trigger the start of a transaction.

I think this would also solve the oft-raised usability issue of SELECT statements needing to be committed when in manual commit mode. It's a bit unintuitive (and generally not wanted) to start a transaction immediately on connecting to a database. (I certainly found this very confusing at first when browsing a production database's data and wondering why no updates were coming in!)

Obviously, any SELECT statements executed after a transaction had been triggered would still need to be committed, but perhaps there could be a clearer visual indicator that a transaction was in progress (e.g. a popup when the transaction starts, with a "don't show again option" for experienced users)?

Btw - I also wanted to say what a great program DBeaver is. It's by far and away the best database client I've used. Thanks for all your hard work!

[serge-rider]

I think this could be a handy improvement.
Having open transaction for select queries may be annoying.

[MatheusNaldi]

+1

[kartoffelsup]

The other way would be less error prone imho. Configuring statements that don't need a transaction. Otherwise strong 👍

[Mudshovel]

This is something that I've been waiting for a while.

+1

[titopepper]

Agree this will be helpful.

[NYP4N]

+1

[jmaynier]

+1
The lack of this feature make Dbeaver less useful than other SQL IDE like Datagrip. Lots of non dev sql users don't understand why they have to rollback when they execute a select with a type or logical error

[serge-rider]

@jmaynier I think you mean PostgreSQL-specific behavior when any SQL error blocks any further SQL queries until you rollback (or commit) current transaction.

[p91paul]

@jmaynier I believe there is a "rollback on error" option somewhere

[ghost]

+1!!!

[quant9]

+1
Even double clicking a table just to view it requires commitment

[WicloW]

when this will be done?

[kseniiaguzeeva]

We are going to do this improvement in 6.3 version. Thank you for remembering this.

[serge-rider]

Problem with PostgreSQL "aborted transaction" was resolved in 6.2.something.
Problem with SELECT queries initiating transaction monitor was solved too.
Moving the rest to 6.3

[Bschitter]

+1 love to see this feature

[serge-rider]

Generally it is working:

Configuring keywords which trigger transaction mode will be implemented as a part of #6528

[p91paul]

which keywords are currently hardcoded? (the answer to this question should better go in a wiki page actually)

[serge-rider]

INSERT,DELETE,UPDATE,MERGE,UPSERT,TRUNCATE in the first alpha version.

Although TRUNCATE is often non-transactional.

I'll create wiki as soon as this feature will be ready. Perhaps some keywords customization will be added in 6.3.2.

[serge-rider]

Also CREATE,DROP,ALTER (for databases which support transactions for DDL).

[p91paul]

what about call/execute?

[p91paul]

also begin/end blocks might call stored procedures

[serge-rider]

Done. We'll check all DML statements (INSERT, UPDATE, etc), all execute statements and all block statements.
Statements customization will be implemented in #6528. Till then we'll use db-dialect-specific keywords.

[kseniiaguzeeva]

Verified

[Bschitter]

Thanks for implementing this feature.

However it seems not to work properly with a PostgreSQL database.
If I have selected "Manual Commit" and "Smart auto-commit" and execute a simple select statement, it will still result in an "idle in transaction" seen in the pg_stat_activity view. As SQL statement it shows "SHOW search_path" and as application_name "DBeaver 6.3.2 - SQLEditor ". Seems to be something DBeaver does for PostgreSQL databases.

While testing this I have noticed that when a PostgreSQL connection is marked as production and is therefore set to "Manual Commit" when opening a connection, there will also be an "idle in transaction" with application_name "DBeaver 6.3.2 - Main" and query:

FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_class c ON c.oid=t.typrelid
WHERE typnamespace=$1
ORDER by t.oid

PostgreSQL version: 11.5
DBeaver version: 6.3.2 (Windows 64-Bit)

[serge-rider]

@Bschitter I think it is the same as #7402
It is not related to smart commit mode. It is how PG JDBC driver behaves.
Let's track this issue in #7402

[serge-rider]

@Bschitter after some testing I can no longer reproduce this.
Whenever you are in manual commit mode Smart auto-commit doesn't make sense (as you already in transaction).
Smart auto-commit works only if you are in auto-commit mode. It switches to Manual commit before you execute any data modifying query.
I think this is how it was supposed to work.
WDYT?

[Bschitter]

@serge-rider sorry, then I missunderstood the feature slightly. Because of your posted screenshot I thought it would be the other way around, that you are in manual commit mode and then have smart auto-commit selected and it always auto commits except if a defined modifying statements is present.

However if I am in auto commit mode with smart auto-commit selected my modifying statement (e.g update TABLE set COLUMN = 'New Name' where id = 1;) is instantly committed.

[serge-rider]

@Bschitter From my perspective smart auto commit shouldn't perform commit implicitly.

In your case whenever you execute a select query (being in manual txn mode) it must switch to auto-commit. But this means it must commit already opened transaction. This doesn't seem right.

However if I am in auto commit mode with smart auto-commit selected my modifying statement (e.g update TABLE set COLUMN = 'New Name' where id = 1;) is instantly committed.

This is a bug then. Although I can't reproduce it. Does it switch to manual mode at all? If yes - does it switch after query execute?
What are your settings (is "separate connection for each editor" on?)

[serge-rider]

@Bschitter Well, I found the bug. It checks query text in case-sensitive mode.
Will be fixed in the next version, thanks for the bug report.

It should work in your version if you will run UPDATE TABLE set COLUMN = 'New Name' where id = 1; (i.e. UPDATE in upper case)

[Bschitter]

@serge-rider indeed it seems to be case sensitive. Now it works, thanks.
And thank you for the support and the quick fix!

[haugs]

Thanks for implementing this great feature! We recently had a lot of trouble with "idle in transaction" sessions in our productive database that we traced back to the support group using DBeaver in manual transaction mode.

The new feature looks very promising and I did some tests. I'd like to suggest two bugfixes/improvements:

1. It works fine for sessions associated with a SQL Editor. But when I double-click a table in the database navigator, the new tab does not have the "Smart auto-commit" option in its GUI at all. The feature should be available for the "Main" session too.
   My people use the visual navigation and data queriyng quite a lot and would need the smart auto-commit in that environment too.

1. When you use the "Auto-Commit plus Smart auto-commit" setting it switches into manual commit mode once you use an UPDATE statement. When you then hit ROLLBACK (or COMMIT) it would be great if it would switch back to Auto-Commit as now there are no more executed modifying statements.

[serge-rider]

@haugs this sounds resonable.
Could you create a new ticket for these improvements?
Just copy-paste you message and refer on this ticket.

[haugs]

Done. I split it into two issues as I expect they can be handled separately.
Keep up the good work! :) 👍