Error when run against Redshift PostgreSQL

[suaswe]

I am trying to run RoundhousE against a Redshift database as per below. I am getting error "42P13: create function must specify volatility attribute (IMMUTABLE|STABLE|VOLATILE)".

rh --connectionstring="host=<host>;user id=<user>;password=<pw>;database=<db>;port=5439" --files=001_test.sql --environmentname=DEV --databasetype=postgres --noninteractive --isuptodate 
42P13: create function must specify volatility attribute (IMMUTABLE|STABLE|VOLATILE) Npgsql.PostgresException (0x80004005): 42P13: create function must specify volatility attribute (IMMUTABLE|STABLE|VOLATILE)    at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at Npgsql.NpgsqlDataReader.NextResult()    at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at Npgsql.NpgsqlCommand.<ExecuteNonQuery>d__92.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at Npgsql.NpgsqlCommand.ExecuteNonQuery()    at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in C:\projects\roundhouse\product\roundhouse.core\databases\AdoNetDatabase.cs:line 143    at roundhouse.databases.AdoNetDatabase.<>c__DisplayClass13_0.<run_sql>b__0() in C:\projects\roundhouse\product\roundhouse.core\databases\AdoNetDatabase.cs:line 130    at Polly.Policy.<>c__DisplayClass108_0.<Execute>b__0(Context ctx, CancellationToken ct)    at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token)    at Polly.Retry.RetryEngine.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Action`4 onRetry, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider)    at Polly.Retry.RetryPolicy.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken)    at Polly.Policy.Implementation(Action`2 action, Context context, CancellationToken cancellationToken)    at Polly.Policy.Execute(Action`2 action, Context context, CancellationToken cancellationToken)    at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in C:\projects\roundhouse\product\roundhouse.core\databases\AdoNetDatabase.cs:line 130    at roundhouse.databases.DefaultDatabase`1.run_sql(String sql_to_run, ConnectionType connection_type) in C:\projects\roundhouse\product\roundhouse.core\databases\DefaultDatabase.cs:line 216    at roundhouse.databases.postgresql.PostgreSQLDatabase.create_or_update_roundhouse_tables() in C:\projects\roundhouse\product\roundhouse.databases.postgresql\PostgreSQLDatabase.cs:line 156    at roundhouse.migrators.DefaultDatabaseMigrator.run_roundhouse_support_tasks() in C:\projects\roundhouse\product\roundhouse.core\migrators\DefaultDatabaseMigrator.cs:line 137    at roundhouse.runners.RoundhouseUpdateCheckRunner.is_database_up_to_date() in C:\projects\roundhouse\product\roundhouse.core\runners\RoundhouseUpdateCheckRunner.cs:line 56    at roundhouse.runners.RoundhouseUpdateCheckRunner.run() in C:\projects\roundhouse\product\roundhouse.core\runners\RoundhouseUpdateCheckRunner.cs:line 43    at roundhouse.console.Program.run_update_check(ConfigurationPropertyHolder configuration)    at roundhouse.console.Program.Main(String[] args) 

Looking at the docs of Redshift (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_FUNCTION.html) vs standard PostgreSQL (https://www.postgresql.org/docs/9.1/sql-createfunction.html) it seems that CREATE FUNCTION in Redshift may require one of the volatility attributes, whereas in standard PostgreSQL it does not. I believe from the RH code that RH does not pass this flag nor give an option to. Is my understanding correct? Might it be feasible to include this feature to allow Redshift to work?

If anyone has worked with RoundhousE and Redshift before, I would love some guidance!

[neoaisac]

Hi, I'm working on a fork of RH that supports Redshift. I'm having it double-checked and should be quickly getting ready for PR... however testing it is difficult because Redshift is an AWS-only platform with very specific ways of working. Anyone can give me some guidance as to what to do for the PR to be accepted?

[erikbra]

@suaswe could you please see if PR #385 solves the issue? If you download the built roundhouse from this build, does it work as expected? https://ci.appveyor.com/project/chucknorris/roundhouse/builds/28078352/artifacts