Support for Redshift statements which cannot be run inside a transaction

[gareth625]

I found that flyway cannot create an external table because it does not recognise it must be run in a transaction. I have forked flyway and believe I have made the necessary addition here gareth625@f6b404d. My Java isn't good enough to find and add a unit tests but I have built the JAR and tested against a Redshift cluster.

Unfortunately I haven't tested the migrations I have included as I'm having trouble accessing my company's AWS resources externally. I wanted to get the ball rolling though and the changes into a commitable state.

What version of Flyway are you using?

4.2.1

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Command line

What database are you using (type & version)?

Redshift

What operating system are you using?

Linux

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

Ran a migration to create an external schema on a Redshift cluster using Athena catalogue which succeeds:

CREATE EXTERNAL SCHEMA IF NOT EXISTS external_schema
  FROM DATA CATALOG
  DATABASE 'flyway'
  REGION 'eu-west-1'
  IAM_ROLE 'arn:aws:iam::123456789012:role/my-iam-role'
  CREATE EXTERNAL DATABASE IF NOT EXISTS;

then tried to create an external table in the schema, for example:

CREATE EXTERNAL TABLE external_schema (
  a_column VARCHAR(6)
)
STORED AS PARQUET
LOCATION 's3://metail-dataeng-snowplow-dev-data/flyway-pr/';

which fails as you cannot create an external table in a transaction.

This I did using two migrations with the flyway command line tool.

What did you expect to see?

Creation of the external schema and table.

What did you see instead?

The creation of the external schema but not the table.

[axelfontaine]

Affected statements:

• ALTER TABLE APPEND
• CREATE EXTERNAL TABLE
• CREATE LIBRARY
• DROP LIBRARY
• DROP TABLE when used with an external table (this one could be harder to fix)
• VACUUM

The current implementation (derived from the PostgreSQL one) also incorrectly looks for

• CREATE DATABASE
• CREATE TABLESPACE
• DROP TABLESPACE
• DROP DATABASE
• ALTER SYSTEM

These checks should be removed.

[axelfontaine]

This has now been fixed. For DROP TABLE when used with an external table the following limitation and workaround has been added to the docs:

Due to Redshift limitations DROP TABLE for external tables cannot run within a transaction, yet Flyway doesn't autodetect this. You can work around this limitation and successfully execute such a statement by including a VACUUM statement in the same SQL file as this will force Flyway to run the entire migration without a transaction.

[gareth625]

Thanks!

[simonejsing]

Redshift also cannot execute an alter table on an external table inside a transaction. It appears this scenario was not covered by this fix.

Was this just missed, or was there a specific reason this could be made to work?

[axelfontaine]

@simonejsing This was most likely missed as it isn't documented anywhere in the Redshift docs. The same workaround as in #1879 (comment) applies. We'll update our docs to reflect this.

[simonejsing]

Thanks, but it is a bit of a clunky work-around since I can't actually vacuum the external table I'm altering. It will have to be a vacuum on some other physical table that is unrelated to the migration...