PostgreSQL compatibility JUnit

[Alfonso Vidal]

I am trying to do JUnit tests with H2, and my APP is configurated to work with PostgreSQL, and with ACL's Spring Boot.

So I have to add into the app the next two lines,

mutableAclService.setClassIdentityQuery("select currval(pg_get_serial_sequence('acl_class', 'id'))");

mutableAclService.setSidIdentityQuery("select currval(pg_get_serial_sequence('acl_sid', 'id'))");

to create the AclService.

I have in the application.properties the configuration spring.datasource.url=jdbc:h2:~/test_db;MODE=PostgreSQL.

But it always returns me, 

org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [select currval(pg_get_serial_sequence('acl_sid', 'id'))]; SQL state [90022]; error code [90022]; Function "PG_GET_SERIAL_SEQUENCE" not found; SQL statement:

select currval(pg_get_serial_sequence('acl_sid', 'id')) [90022-197]; nested exception is org.h2.jdbc.JdbcSQLException: Function "PG_GET_SERIAL_SEQUENCE" not found; SQL statement

...

Anyone knows how to fix it?

[Evgenij Ryazanov]

Hello.

In general case, if you want to use multiple databases, you should use only standard SQL features. However, the SQL Standard does not provide any way to fetch the current value of the sequence. Such operation has very low meaning. There are other ways to fetch the generated value during insertion into table. For example, the JDBC has methods and parameters for generated keys. The SQL Standard also has own capabilities. But it looks like you use some weird service that can't use any sane and reliable ways.

For H2 1.4.197 (this version is outdated, BTW) you can use something like

SELECT CURRVAL(SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'acl_class' AND COLUMN_NAME = 'id')

instead.

Names of table ('acl_class') and column ('id') may need to be written in upper or lower case depending on way how they were created.

[Evgenij Ryazanov]

You can also create a user-defined function with name pg_get_serial_sequence and execute the inner query in it, if you wish.

[Noel Grandin]

On 2020/01/23 11:44 AM, Alfonso Vidal wrote:
> I am trying to do JUnit tests with H2, and my APP is configurated to work with PostgreSQL, and with ACL's Spring Boot.
>

We have a variety of shims for our Postgresql mode in pg_catalog.sql, but we only cover the basics (as you discovered)

I suggest trying to create an ALIAS that does the right thing, and then either submit a PR with that change, or if that
is too much, just log a bug with the ALIAS you created and we will get around to adding it to our existing set.

Regards, Noel.

[Alfonso Vidal]

Thanks a lot! It works!