hibernate migration an MODE=PostgreSQL

Andrea Spacca

unread,

Nov 18, 2021, 2:55:54 AM11/18/21

to H2 Database

I have the following hibernate properites:

hibernate.connection.url: jdbc:h2:/app/h2/test;TRACE_LEVEL_FILE=3;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;TRACE_LEVEL_SYSTEM_OUT=3

hibernate.connection.driver_class: org.h2.Driver

hibernate.dialect: org.hibernate.dialect.PostgreSQL10Dialect

hibernate.connection.username: username

hibernate.connection.password: password

when restarting my application hibernate checks in the `information_schema.sequences` table and fail to find `start_value` field

/**/PreparedStatement prep0 = conn0.prepareStatement("select * from information_schema.sequences");

2021-11-18 07:33:41 jdbc[3]: Plan : calculate cost for plan [sequences:-9:org.h2.table.MetaTable@61b5969]

2021-11-18 07:33:41 jdbc[3]: Plan : for table filter sequences:-9:org.h2.table.MetaTable@61b5969

2021-11-18 07:33:41 jdbc[3]: Table : potential plan item cost 10,000 index meta

2021-11-18 07:33:41 jdbc[3]: Plan : best plan item cost 10,000 index meta

2021-11-18 07:33:41 jdbc[3]: Plan : plan cost 10,001

2021-11-18 07:33:41 jdbc[3]:

/**/ResultSet rs0 = prep0.executeQuery();

2021-11-18 07:33:41 jdbc[3]:

/*SQL #:1 t:1*/select * from information_schema.sequences;

2021-11-18 07:33:41 jdbc[3]:

/**/rs0.next();

2021-11-18 07:33:41 jdbc[3]:

/**/rs0.getString("sequence_catalog");

2021-11-18 07:33:41 jdbc[3]:

/**/rs0.getString("sequence_schema");

2021-11-18 07:33:41 jdbc[3]:

/**/rs0.getString("sequence_name");

2021-11-18 07:33:41 jdbc[3]:

/**/rs0.getLong("start_value");

2021-11-18 07:33:41 jdbc[3]: exception

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "start_value" not found [42122-200]

according to documentation there is a `current_value`, a `min_value` and a `max_value`:

http://h2database.com/html/systemtables.html

how to overcome this?

Evgenij Ryazanov

unread,

Nov 29, 2021, 8:39:44 PM11/29/21

to H2 Database

Hello.

H2 2.0.202 has INFORMATION_SCHEMA.SEQUENCES.START_VALUE, but usage of PostgreSQL***Dialect with H2 may still fail at any point, H2 should only be used with H2Dialect.

Andrea Spacca

unread,

Dec 2, 2021, 2:59:44 AM12/2/21

to H2 Database

I saw the change in 2.0.202 and tried with that version

without `MODE=PostgreSQL` and with `org.hibernate.dialect.H2Dialect` I get the following error:

```

ERROR: NULL not allowed for column "ID"; SQL statement:

insert into users (id, password, session_id, username) values (null, ?, ?, ?) [23502-202]

```

with `MODE=PostgreSQL` and `org.hibernate.dialect.PostgreSQL10Dialect` I get this one:

```

ERROR: Sequence "USERS_ID_SEQ" not found; SQL statement:

select currval('users_id_seq') [90036-202]

```

These are complete new errors that I didn't have on 1.4.200

> usage of PostgreSQL***Dialect with H2 may still fail at any point, H2 should only be used with H2Dialect.

that's not what the documentation says: at the contrary it advises to not use the H2Dialect in compatibility mode (while not all the features might be supported)

https://h2database.com/html/tutorial.html?highlight=dialect&search=dialect#using_hibernate

Noel Grandin

unread,

Dec 2, 2021, 4:27:42 AM12/2/21

to h2-da...@googlegroups.com, Andrea Spacca

On 2021/12/02 9:59 am, Andrea Spacca wrote:
>
> without `MODE=PostgreSQL` and with `org.hibernate.dialect.H2Dialect` I get the following error:
> ```
> ERROR: NULL not allowed for column "ID"; SQL statement:
> insert into users (id, password, session_id, username) values (null, ?, ?, ?) [23502-202]
> ```
>

What does the definition for the users table look like? This seems like some kind of issue with auto-generated key columns.

> with `MODE=PostgreSQL` and `org.hibernate.dialect.PostgreSQL10Dialect` I get this one:
> ```

The "MODE=" stuff is essentially unsupported at this point - none of the existing maintainers are interested,
it looks like Hibernate is looking for metadata that H2 doesn't know how to emulate.

Evgenij Ryazanov

unread,

Dec 2, 2021, 7:12:00 AM12/2/21

to H2 Database

Hello.

Hibernate produces wrong SQL for H2 because H2IdentityColumnSupport.getIdentityInsertString() incorrectly returns "null" instead of "default" for a some historic reason.

`getIdentitySelectString() also needs to be updated.

There is a workaround described in another thread, but actually the whole H2Dialect may need to be reviewed to support H2 2.0 properly.