Hi,
I’m trying to alter a column datatype in redshift using modifyDataType and running into this error:
Unexpected error running Liquibase: ERROR: ALTER TABLE ALTER COLUMN cannot run inside a transaction block [Failed SQL: (0)
is there a work around for this in liquibase, my reading suggests with other client tools you can turn autocommit on then do the change then turn it off again but I’m not sure I see a way to that here?
Welcome to the Liquibase Forum, Elvis!
I did some digging and found this documentation that may help: Using Liquibase with Redshift | Liquibase Docs
It says that modifyDataType is supported for AWS Redshift on Liquibase, with further details here: modifyDataType | Liquibase Docs
I’m going to ask a couple of colleagues about a work around, but please let me know if the information above is any help.
Kindly,
Tabby
Liquibase Technical Community Manager
Seems like a bug in the Liquibase driver that needs to turn off auto-commit when running DDL (or at least alter statements).
Looks like it’s the other way around, the fix (when using other client tools) is to turn autocommit on then do the DDL then turn it off (or make sure the change is made outside of a transaction).
Liquibase tries to run everything in transactions so that if things fail half way through there is a chance to clean things up.
There are statements which the database doesn’t support that for, though. We should be catch and handle that and it looks like for this case we aren’t. Could you create an issue in Issues · liquibase/liquibase-redshift · GitHub about how modifyDataType does not support running in a transaction in redshift?
BUT: for times liquibase can’t correctly detect that or isn’t handling it as it should, you can control it yourself with a runInTransaction="false" attribute to changeSets. You should be able to add that to the needed changesets for now until the code can be updated to handle it automatically.
Nathan
OK new problem:
Getting this error when trying to use modifyDataType even with runInTransaction: false
Unexpected error running Liquibase: ERROR: ALTER TABLE ALTER COLUMN does not support USING expression [Failed SQL: (0) ALTER TABLE grandcare_datalake.rs_staging_como_devicemodels_latest ALTER COLUMN url TYPE VARCHAR(max) USING (url::VARCHAR(max))]
Another bug, or should I add to the existing one?
