Better support for DDL statements that cannot run within a transaction

[pards]

Overriding DbSupport.supportsDdlTransactions does not prevent FlyWay from attempting to execute DDL statements in a transaction.

I was trying to implement Netezza support, but found that ALTER TABLE statements fail when executed in a transaction.

See
http://www-01.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_func_categories_transaction_control.html

[pards]

[ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:0-SNAPSHOT:migrate (default-cli) on project rdr-database: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Error executing statement at line 1: alter table person
[ERROR] add name varchar(30): ERROR:  Add column may not be called in a transaction block or an autocommit off block of a stored procedure
[ERROR] -> [Help 1]
[ERROR]

Despite having this in NetezzaDbSupport.java

@Override
public boolean supportsDdlTransactions() {
    // NZ doesn't fully support all DDL operation in transactions, i.e. alter table
    return false;
}

[axelfontaine]

As per JavaDoc: Checks whether ddl transactions are supported for this database.

It is purely informative, as to how to behave in case of failure.

Which DDL statement is failing? Is it part of a regular migration? What other limitations does Netezza have? (Not all abstractions to support them may be present in the core yet)

[axelfontaine]

Any news?

[pards]

ALTER TABLE statements fail
It's part of a regular migration.

See the Netezza documentation for further information.
http://www-01.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_func_categories_transaction_control.html

Basically, the following list of statements are NOT permitted within a transaction

• BEGIN
• [CREATE | DROP] DATABASE
• ALTER TABLE [ADD | DROP] COLUMN operations
• SET AUTHENTICATION
• [SET | DROP] CONNECTION
• GROOM TABLE
• GENERATE STATISTICS
• SET SYSTEM DEFAULT HOSTKEY
• [CREATE | ALTER|DROP] KEYSTORE
• [CREATE | DROP] CRYPTO KEY
• SET CATALOG
• SET SCHEMA dbname.schemaname, where dbname is not the current database

[axelfontaine]

Ok, thanks. Let's revisit this in the 3.2 timeframe to see if the necessary abstractions can be put in place to make this easier to implement.

[kryptt]

Is there currently any way to get arround this ?

I am getting ERROR: ALTER TYPE ... ADD cannot run inside a transaction block for a postgres migration myself..

[pards]

Not to my knowledge.
Netezza is forked from Postgres, so it sounds like the same problem

[kryptt]

I stumbled onto the limitations section of the postgres page for your docs:
http://flywaydb.org/documentation/database/postgresql.html

and managed to re-write the query in a transaction safe way (pasting it here, if someone else stumbles across this:

ALTER TYPE action_type RENAME TO stale_action_type;

CREATE TYPE action_type AS ENUM(
  'loginFailure',
  'loginSuccess',
  ...
);

ALTER TABLE mytable ALTER COLUMN action TYPE action_type USING action::text::action_type;

DROP TYPE stale_action_type;

[trygvis]

@kryptt Thanks!

For the archive, you can use full expressions for the USING clause:

ALTER TABLE foo
ALTER COLUMN bar TYPE new_type USING (
CASE bar :: TEXT
WHEN 'AAA'
  THEN 'BBB'
ELSE bar :: TEXT END) :: new_type;

[realsonic]

@kryptt Thanks a lot for W/A. I have the same problem too.

[axelfontaine]

This has been fixed for PostgreSQL via auto-detection. Please open new issues for other databases.