Add support for AWS Aurora DSQL

[mfridman]

https://aws.amazon.com/rds/aurora/dsql/

I think there should be a way to get this working, only a few unsupported features which we don't use in goose (I believe).

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility.html

Gotchas

ERROR: IDENTITY constraint is not supported (SQLSTATE 0A000)

ERROR: failed to create version table: failed to insert version 0: ERROR: ddl and dml are not supported in the same transaction (SQLSTATE 0A000)

[mfridman]

Nit, super annoying that DSQL chose to:

1. Add new syntax, like forcing ASYNC on CREATE INDEX

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-create-index-async.html#working-with-create-index-syntax

2. Prevent specifying things like COLLATION explicitly. Sure the default is "[...] the C collation only." But at least let me specify that and accept only that.

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-unsupported-features.html#working-with-postgresql-compatibility-unsupported-expressions

Cloud providers typically use something like (GCP Cloud SQL, postgres):

If you don't specify a character set and collation when you create your database, the database will have the following default values:

Character set: UTF8 Collation: en_US.UTF8

This means schemas that may work with native Postgres either will not or may behave differently, and thus are not portable.

[mfridman]

Alright, I got this working by making a few changes to goose itself.

1. Update CreateTable and InsertVersion (%s is a placeholder for the table name)

✅ Added in https://github.com/pressly/goose/pull/971

CREATE TABLE %s (
	id integer PRIMARY KEY,
	version_id bigint NOT NULL UNIQUE,
	is_applied boolean NOT NULL,
	tstamp timestamp NOT NULL DEFAULT now()
)

INSERT INTO %s (id, version_id, is_applied) 
	VALUES (
	COALESCE((SELECT MAX(id) FROM %s), 0) + 1, 
	$1, 
	$2
)

2. Add a new provider option, which is useful beyond just DSQL

✅ Added in #970

// WithIsolateDDL enables transaction isolation for DDL changes, required by some databases like AWS
// Aurora DSQL that don't support mixing DDL and DML in the same transaction.
func WithIsolateDDL(b bool) ProviderOption {
	return configFunc(func(c *config) error {
		c.isolateDDL = b
		return nil
	})
}

3. Add a callback for classifying errors, which is generally helpful for callers to handle errors based on their driver. E.g.,

# On your side (the client)
goose.WithContinueOnError(func(err error) bool {
	if pgErr := (&pgconn.PgError{}); errors.As(err, &pgErr) {
		switch pgErr.Code {
		case "42P07": // duplicate table error
			return true
		}
	}
	return false
}),

# On goose side (callback)
if _, err := db.ExecContext(ctx, stmt); err != nil {
	if p.cfg.continueOnError != nil && p.cfg.continueOnError(err) {
		// If the error classifier returns true, we consider the error as non-fatal and
		// continue executing the remaining statements.
		if p.cfg.verbose {
			p.cfg.logger.Printf("Non-fatal error: %v", err)
		}
		continue
	}
	return err
}