H2 In Postgresql Mode: Function current_schemas not found?

[Robert M]

Hello all.

I'm currently running H2 1.3.174 and get the following error when I try to connect the web console to my H2 server running in Postgresql mode. The web console still connects but this error is still displayed in the left-hand panel after I connect. Any ideas?

Thanks for your time!
-Robert



Details are below:


I run the H2 server inside a simple Java class which I run in debug mode in Eclipse.
import org.h2.tools.Server;

public class H2Test {
    public static void main(String[] args) throws Exception {
        String[] serverArgs = {"-pgAllowOthers"};
        Server server = Server.createPgServer(args).start();
        server.stop(); // i set a break point here
    }
}

Then I start the web console like so:
java -cp ./h2-1.3.174.jar:./postgresql-9.3-1100.jdbc4.jar org.h2.tools.Server -web

When I open the web console at http://localhost:8082, I see the following:
ERROR: Function "CURRENT_SCHEMAS" not found; SQL statement:
SELECT nspname AS TABLE_SCHEM , NULL AS TABLE_CATALOG  FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM [90022-174]
  Detail: org.h2.jdbc.JdbcSQLException: Function "CURRENT_SCHEMAS" not found; SQL statement:
SELECT nspname AS TABLE_SCHEM , NULL AS TABLE_CATALOG  FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM [90022-174] 90022/0
org.postgresql.util.PSQLException: ERROR: Function "CURRENT_SCHEMAS" not found; SQL statement:
SELECT nspname AS TABLE_SCHEM , NULL AS TABLE_CATALOG  FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM [90022-174]
  Detail: org.h2.jdbc.JdbcSQLException: Function "CURRENT_SCHEMAS" not found; SQL statement:
SELECT nspname AS TABLE_SCHEM , NULL AS TABLE_CATALOG  FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM [90022-174]
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
    at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getSchemas(AbstractJdbc2DatabaseMetaData.java:2251)
    at org.postgresql.jdbc3.AbstractJdbc3DatabaseMetaData.getSchemas(AbstractJdbc3DatabaseMetaData.java:378)
    at org.h2.bnf.context.DbContents.getSchemaNames(DbContents.java:202)
    at org.h2.bnf.context.DbContents.readContents(DbContents.java:165)
    at org.h2.server.web.WebApp.tables(WebApp.java:666)
    at org.h2.server.web.WebApp.process(WebApp.java:223)
    at org.h2.server.web.WebApp.processRequest(WebApp.java:168)
    at org.h2.server.web.WebThread.process(WebThread.java:138)
    at org.h2.server.web.WebThread.run(WebThread.java:94)
    at java.lang.Thread.run(Thread.java:695)

[Noel Grandin]

It doesn't look like we support postgresql JDBC drivers > 9.2.

If you want to add support, the relevant code is in:
src/main/org/h2/server/pg/pg_catalog.sql

It looks like we are missing support for the current_schemas(boolean) function/alias.
http://www.postgresql.org/docs/8.4/static/functions-info.html

[Robert M]

I tried an earlier driver last week(9.0?) and had the same issue. Plus looking at the PgServer's getVersion method shows a returned value of "PostgreSQL 8.1.4 server protocol using H2...."
so I'm guessing using drivers older than 8.1.4 may be problematic.

Is Postgresql mode for H2 not widely used then?

Thanks!
-Robert

[Noel Grandin]

Not a lot of people seem to be using it, no.

Andrew Franklin did some work recently on updating H2's support for the PostgreSQL JDBC driver in the following issues:
481, 472, 477, 473 and 475.

Judging by the comments in
https://code.google.com/p/h2database/issues/detail?id=472
he seemed to think he had added support for versions of PostgresSQL from 8.1 to 9.2.

[Robert M]

I'm probably mistaken then in that I used a driver less than 9.2 last week.

Thanks for the help Noel!
-Robert