Redshift migrations break from 0.9.13 to 0.9.14

[jlippold]

When running a fresh migration from a blank db on amazon redshift, the script dies

We are currently using 0.9.13 where redshift was still supported. Maybe 0.9.14 added some new postgres command that redshift doesn't support?

[wzrdtales]

@jlippold No there are no new commands in any of the >0.9.13 versions.

Actually the message tells us you're crashing in the all function:

node-db-migrate/lib/driver/pg.js

Line 331 in 9afc4cc

params[1](err, result.rows);

The all function gets only called by System functions, for example when creating a migration table. The query itself doesn't return a result in this case and thus throwing this error.

Can you please rerun this with verbose output and provide everything necessary to reproduce this? Otherwise it's hard to help you.

Thank you

[wzrdtales]

@jlippold In v0.9.14 the createMigrationsTable function has been changed. It now uses all and used before runSql, this was not intended and was a bug.

node-db-migrate/lib/driver/pg.js

Lines 123 to 179 in 918df65

	createMigrationsTable: function(callback) {
	var options = {
	columns: {
	'id': { type: type.INTEGER, notNull: true, primaryKey: true, autoIncrement: true },
	'name': { type: type.STRING, length: 255, notNull: true},
	'run_on': { type: type.DATE_TIME, notNull: true}
	},
	ifNotExists: false
	};
	this.all('select version() as version', function(err, result) {
	if (err) {
	return callback(err);
	}
	if (result && result && result.length > 0 && result[0].version) {
	var version = result[0].version;
	var match = version.match(/\d+\.\d+\.\d+/);
	if (match && match[0] && semver.gte(match[0], '9.1.0')) {
	options.ifNotExists = true;
	}
	}
	// Get the current search path so we can change the current schema
	// if necessary
	this.all("SHOW search_path", function(err, result) {
	if (err) {
	return callback(err);
	}
	var searchPath;
	// if the user specified a different schema, prepend it to the
	// search path. This will make all DDL/DML/SQL operate on the specified
	// schema.
	if (this.schema === 'public') {
	searchPath = result[0].search_path;
	} else {
	searchPath = this.schema + ',' + result[0].search_path;
	}
	this.all('SET search_path TO ' + searchPath, function() {
	this.all("SELECT table_name FROM information_schema.tables WHERE table_name = '" + global.migrationTable + "'", function(err, result) {
	if (err) {
	return callback(err);
	}
	if (result && result && result.length < 1) {
	this.createTable(global.migrationTable, options, callback);
	} else {
	callback();
	}
	}.bind(this));
	}.bind(this));
	}.bind(this));
	}.bind(this));
	},

This means one of the following queries was broken even before 0.9.14

SELECT table_name FROM information_schema.tables WHERE table_name = 'migrations'
SET search_path TO 'public'
SHOW search_path
select version() as version

But this would be strange, because if one of these queries fails, then no migration table would be created at all and this also should'nt work with <0.9.13

[jlippold]

it's SET search_path TO $user, public that's returning the error

{ [error: syntax error at or near "$"]
  name: 'error',
  length: 126,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '20',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: '/home/awsrsqa/padb/src/pg/src/backend/parser/parser_scan.l',
  line: '695',
  routine: 'yyerror' }

I'm not sure if that helps, i'm digging deeper

[wzrdtales]

This error is generated by the lack of quotes. This have to be at least:
SET search_path TO "$user", public

But db-migrate does generate the correct query, I just switched to the 0.9.14 version and tested it. The following query gets generated:

 SET search_path TO test_schema,"$user",public

[jlippold]

This sql

node-db-migrate/lib/driver/pg.js

Line 148 in 918df65

this.all("SHOW search_path", function(err, result) {

returns the result differently when using redshift

In the latest postgres SHOW search_path returns "$user",public
While in redshift SHOW search_path returns $user,public (no quotes)

Which explains the error.. not too sure the best way to fix just yet

[wzrdtales]

ahh, well ok. Now we come close to a solution :)

give me a couple of minutes.

[jlippold]

this sql

node-db-migrate/lib/driver/pg.js

Line 133 in 918df65

this.all('select version() as version', function(err, result) {

returns this on redshift:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, 
compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), 
Redshift 1.0.946

and on regular postgres:

PostgreSQL 9.3.5 on x86_64-apple-darwin14.0.0, 
compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 
64-bit

I suppose if you detect the Redshift string in the returned version, you can add the fix based on that condition?

[wzrdtales]

75e1b17

Waiting for travis tests now

[jlippold]

that commit works for me on redshift 👍

[wzrdtales]

Going to be published as soon as all travis tests are finished.
Version that is going to hold this fix: 0.9.17

[jlippold]

you're the man

[wzrdtales]

applied to master, published to v0.9.17