DE34272 / TSIM-915: Receive "ERROR: The session is read-only" using pre/post SQL option in the Input Data tool when attempting to perform any operation that is not a select statement

[ntobon]

Alteryx Designer

• All Alteryx version
• Amazon Redshift
• Amazon Redshift Drivers
  • Simba Amazon Redshift 1.04.21.1000
  • Amazon Redshift version 1.4.34.1000

When attempting to run any command that is not a select statement, such as dropping/creating tables with regular Input Tool using pre/post SQL option you receive the following error message:

Input Data (2) Executing PostSQL: "drop table "public".nt_sales4;" : [Simba][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 59001] ERROR: The session is read-only

image.png
The issue occurs when using Driver Simba Amazon Redshift 1.04.21.1000 or Driver Amazon Redshift version 1.4.34.1000.
The issue is not observed when using old driver Amazon Redshift 1.04.13.1000.

Defect: DE34272 / TSIM-915
Status: Fixed in version 2021.1.

Workarounds

Workaround #1:
Use driver Amazon Redshift 1.04.13.1000

Workaround #2:
You can manually turn Read Only off in a pre-sql statement by doing this: 

SET ReadOnly = 0;

image.png


Additional Resources

Receive Error when attempting to run "drop table" command with IN-DB tool

 

[ScotiaTheOne]

Hi ntobon,

Has this been resolved? I'm able to run my flow with an INSERT in the post-SQL statement on my local machine, but can't run the same flow on a remote machine.

My local machine is running v1.2.6.1006 (64 bit)

My remote machine is running v1.4.16.1000 (64 bit)

How do I resolve this?

Many thanks

Scott

[ntobon]

@ScotiaTheOne The defect is still open, but we have identified a workaround. Please try workaround #2.

[ScotiaTheOne]

Doesn't work in a Post-SQL statement though does it? thanks

[ntobon]

@ScotiaTheOne  workaround #2 should also work for Post-SQL statement

[ScotiaTheOne]

Got thsi working, thanks @ntobon

Works now in Post-SQL with the SET ReadOnly = 0; at the start of the query