Duplicate column name on H2 with MODE=PostgreSQL

[kressi]

Running migrations on H2 with MODE=PostgreSQL causes following exception.

io.r2dbc.spi.R2dbcBadGrammarException: Duplicate column name "?column?"; SQL statement:
insert into "migrations_lock"(id, locked) select * from (select 1, false) x  where not exists(select * from "migrations_lock") [42121-214]

I am not sure whether that's an issue of r2dbc-migrate or r2dbc-h2. I don`t understand why that issue occurs.

• Versions
  • name.nkonev.r2dbc-migrate:r2dbc-migrate-spring-boot-starter:2.7.6
  • org.springframework.boot:spring-boot-starter-parent:2.7.1
• Complete logs
• application.yml

[nkonev]

Try to force postgres dialect, e. g.

spring:
  r2dbc:
    url: r2dbc:h2:mem:///testdb;MODE=PostgreSQL
    username: sa
    password: ''

r2dbc:
  migrate:
    dialect: POSTGRESQL
    resources-paths:
      - classpath:/db/migration/*.sql

[kressi]

Now I get a syntax error instead of duplicate column.

io.r2dbc.spi.R2dbcBadGrammarException: Syntax error in SQL statement "insert into ""migrations_lock""(id, locked) values (1, false) [*]on conflict (id) do nothing"; SQL statement:
insert into "migrations_lock"(id, locked) values (1, false) on conflict (id) do nothing [42000-214]

• https://github.com/kressi/r2dbc-migration-h2-issue/runs/7161093152?check_suite_focus=true#step:5:965

[nkonev]

@kressi

It seems the issue is caused by quoted table names

But H2 migrations are works without Postgres compatibility mode.

I also tried to provide custom dialect to avoid quotes but H2 in Postgres compatibility mode isn't able to parse modern Postgres syntax

@Component
    public static class SimplePostgresqlDialect implements SqlQueries {
        @Override
        public List<String> createInternalTables() {
            return Arrays.asList(
                    "create table if not exists simple_migrations(id int primary key, description text)",
                    "create table if not exists simple_migrations_lock(id int primary key, locked boolean not null)",
                    "insert into simple_migrations_lock(id, locked) values (1, false) on conflict (id) do nothing"
            );
        }

        @Override
        public String getMaxMigration() {
            return "select max(id) from simple_migrations";
        }

        public String insertMigration() {
            return "insert into simple_migrations(id, description) values ($1, $2)";
        }

        @Override
        public Statement createInsertMigrationStatement(Connection connection, FilenameParser.MigrationInfo migrationInfo) {
            return connection
                    .createStatement(insertMigration())
                    .bind("$1", migrationInfo.getVersion())
                    .bind("$2", migrationInfo.getDescription());
        }

        @Override
        public String tryAcquireLock() {
            return "update simple_migrations_lock set locked = true where id = 1 and locked = false";
        }

        @Override
        public String releaseLock() {
            return "update simple_migrations_lock set locked = false where id = 1";
        }
    }

io.r2dbc.spi.R2dbcBadGrammarException: Syntax error in SQL statement "insert into simple_migrations_lock(id, locked) values (1, false) [*]on conflict (id) do nothing"; SQL statement:
insert into simple_migrations_lock(id, locked) values (1, false) on conflict (id) do nothing [42000-214]

This library is heavy relies on upsert-like syntax CREATE TABLE .. ON CONFLICT DO NOTHING so I can conclude that it's no way to start it against H2 in Postgres compatibility mode.

[nkonev]

You can play with tuning dialect or (I recommend) to use testcontainers as I did https://github.com/nkonev/r2dbc-migrate-example, because testing with testcontainers runs real Postgres.