I have my Oracle SQL Developer, which is connecting to PostgreSQL database
Following this link : Oracle SQL Developer and PostgreSQL I have managed to connect my SQLDeveloper to PostgreSQL (FYI, my SQLDeveloper version is 17.2, and the postgresql in the server is 9.6)
However, eventhough my autocommit setting is off in my SQLDeveloper (through Tools > Preferences > Database > Advanced), my SQLDeveloper cannot commit and rollback. When I tried to execute commit/rollback manually (i.e. by typing "COMMIT;" or "ROLLBACK;"), I got the message :
Cannot rollback when autoCommit is enabled.
As far as I know, PostgreSQL is always autocommit, and the autocommit off is done by the client. For example, I tried PGAdmin, and manage to commit and rollback manually without autocommit (too bad, PGAdmin is a little bit slow)
Can anyone help me turning off the autocommit for SQLDeveloper when connecting to PostgreSQL?
Thanks
This can be done, but pgjdbc driver has to be modified (autocommit, can not be modified using url properties)
I have done it with release 42.0.0 (and can send the jar by email if needed):
https://github.com/pgjdbc/pgjdbc download source code
modify code as described here:
PGProperty.java
DEFAULT_ROW_FETCH_SIZE("defaultRowFetchSize", "50", // was "0"
AUTOSAVE("autosave", "always", // was "never"
PgConnection.java private boolean autoCommit = false; // was true
rebuild the jar file.
This modified driver supports commit; and rollback; in syntax window even if commit and rollback buttons are still greyed.
Maybe somebody will find a way to change Sql developer code to enable those buttons ;o)
nb:
defaultrowfetchsize has been modified to prevent sql developer from loading big tables in memory.
autosave has been modified to prevent ERROR: current transaction is aborted, commands ignored until end of transaction block.