Support Redshift

[gmcguire]

Loving this tool for our Postgres 9.x ecosystem. We are beginning to roll out a multi-schema Redshift data warehouse, and I'm investigating this for adaptation. Looks like for our use case there are two major issues/enhancements needed:

1. Remove requirement for PL/pgSQL. (Redshift doesn't support it or any user defined functions.) Aside from the bootstrap s-e-o data model I don't see it in use.
2. Support multiple schemas. We are using Postgres schemas for multi-tenancy and would need to apply changes across one or all schemas in a database.

I can work on these if it's something you are interested in, but wanted your input first. Thanks!

[mbryzek]

Thanks! Would love to have this work for redshift.

1: Agreed - let me think of best way to do this, but probably will just remove the script entirely from sem (it's orthogonal to management of scripts and can easily be its own script). Only wrinkle is getting sem to bootstrap itself - and rewriting that from pl/pgsql to probably a set of ruby scripts. If you already have a patch or want to write one - that would be great!

1. interesting - do you think this should be an intrinsic feature of sem or of a script outside of sem that just runs sem-apply multiple times?

[gmcguire]

@mbryzek I don't have any patches yet, but would be happy to write some. I don't know any ruby, but can learn.

For the multi-schema support I think it would be best as a feature of sem, because the scripts data model will (probably?) need an additional schema column to reflect the schema that was patched. I was thinking it could be null by default to keep the existing schema-less behavior, but the unique constraint could be changed to include (schema, filename). If you specified a command-line list of one or more schemas you'd just loop over them, populating the schema column of the scripts table as you go. Otherwise you just use the default behavior of current schema and insert NULL for the schema.

[khy]

So, I ran into the same issues when attempting to use with Redshift. To workaround, I did the following manually, which has worked so far:

dw=# create schema schema_evolution_manager;
CREATE SCHEMA
dw=# create table schema_evolution_manager.scripts (
dw(#   id           bigint identity not null primary key,
dw(#   filename     varchar(100) not null unique,
dw(#   created_at   timestamp default getdate() not null
dw(# );
CREATE TABLE
dw=# create table schema_evolution_manager.bootstrap_scripts (
dw(#   id           bigint identity not null primary key,
dw(#   filename     varchar(100) not null unique,
dw(#   created_at   timestamp default getdate() not null
dw(# );
CREATE TABLE
dw=# insert into schema_evolution_manager.bootstrap_scripts (filename) values ('20130318-105434.sql');
INSERT 0 1
dw=# insert into schema_evolution_manager.bootstrap_scripts (filename) values ('20130318-105456.sql');
INSERT 0 1

Notes:

• by "works so far", I mean I was able to run one (1) script using sem-apply. I'll update if anything goes wrong going forward.
• obviously, none of the scripts in https://github.com/mbryzek/schema-evolution-manager/blob/master/scripts/20130318-105456.sql will work (this is not a problem for my use case).
• the types of the tables were changed to be Redshift-compatible. I've convinced myself that they are equivalent, but it's possible that they are not.