Support PostgreSQL Dialect Cloud Spanner Database

[carlosgfs]

Which version and edition of Flyway are you using?

10.10.0

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java API

Which database are you using? (Type & version)

Cloud Spanner DB with PostgreSQL Dialect

Which operating system are you using?

MacOS

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

Setup flyway spanner client v10.10.0 like this:

runtimeOnly "org.flywaydb:flyway-gcp-spanner:10.10.0"

  flyway:
    enabled: true
    locations: db/migration
    baseline-on-migrate: true

on my db/migration I have the initial schema with the existing DB tables. When I start my application I get this error:

{"message":"Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'jdbcMappingContext' defined in class path resource [com/forescout/soaradminservice/spanner/configuration/JdbcConfiguration.class]: Unsatisfied dependency expressed through method 'jdbcMappingContext' parameter 1; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jdbcCustomConversions' defined in class path resource [com/forescout/soaradminservice/spanner/configuration/JdbcConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.data.jdbc.core.convert.JdbcCustomConversions]: Factory method 'jdbcCustomConversions' threw exception; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'jdbcDialect' defined in class path resource [com/forescout/soaradminservice/spanner/configuration/JdbcConfiguration.class]: Unsatisfied dependency expressed through method 'jdbcDialect' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration  failed\n-----------------\nSQL State  : null\nError Code : 3\nMessage    : INVALID_ARGUMENT: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: [ERROR] syntax error at or near \"OPTIONS\"; failed to parse the DDL statements.\nLocation   :  ()\nLine       : 1\nStatement  : CREATE TABLE flyway_schema_history (\n    installed_rank INT64 NOT NULL,\n    version STRING(50),\n    description STRING(200) NOT NULL,\n    type STRING(20) NOT NULL,\n    script STRING(1000) NOT NULL,\n    checksum INT64,\n    installed_by STRING(100) NOT NULL,\n    installed_on TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),\n    execution_time INT64 NOT NULL,\n    success BOOL NOT NULL\n) PRIMARY KEY (installed_rank DESC)","eventTime":"2024-03-26T16:21:20.234Z","severity":"WARN","thread":"main","serviceContext":{"service":"bq-core","version":"1.0.0"}}

after parsing the error the create table statement for the history table is:

CREATE TABLE flyway_schema_history (
	installed_rank INT64 NOT NULL,
	version STRING(50),
	description STRING(200) NOT NULL,    
	type STRING(20) NOT NULL,    
	script STRING(1000) NOT NULL,    
	checksum INT64,    
	installed_by STRING(100) NOT NULL,    
	installed_on TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
	execution_time INT64 NOT NULL,
    success BOOL NOT NULL
) PRIMARY KEY (installed_rank DESC)

Which I think is intended for GoogleSQL dialect not PostgresSQL dialect

What did you expect to see?

Expected to run the baseline migration for my GCP Spanner Database

What did you see instead?

Error while creating flyway_schema_history Table

Please let me know if PostgreSQL dialect will be supported for Spanner DB