How can I find the current value for AUTOCOMMIT in my redshift / SQL Workbench connection?

Question

I would like to "investigate" a bit the behavior of the Redshift AutoCommit setting through SQL Workbench / JDBC connection.

I can choose the setting on the connection page, or impose it through "SET AUTOCOMMIT ON/OFF"

But how can I find its current value within my connection?

The documentation for the SET command does not give much...

and SHOW ALL does not mention this parameter...

Answer 1

I am using psql client for connecting to Redshift.When I try to SET autocommit to OFF I'm getting an error saying ERROR: SET AUTOCOMMIT TO OFF is no longer supported, which sounds like you cannot perform any operation in Redshift without a transaction, though I did not find any documentation about it.

psql \
--host=localhost \
--username=xxx \
--port=6506 \
--dbname=datalake
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04), server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

datalake=# SHOW AUTOCOMMIT;
 autocommit 
------------
 on
(1 row)

datalake=# SET AUTOCOMMIT=false;
ERROR:  SET AUTOCOMMIT TO OFF is no longer supported

Answer 2

There is no "SQL" command that can do that in SQL Workbench/J

The only way you can currently see it, it by looking at the menu item. If the Autocommit item is selected, then it's turned on.

Alternatively you can look at the Commit or Rollback menu items. If they are disabled, then autocommit is turned on as well.