opentable/otj-pg-embedded

ERROR: syntax error at or near "PROCEDURE"

atulg-fw opened this issue · 5 comments

atulg-fw commented

I am facing an issue when I'm trying to create a PROCEDURE

EmbeddedPostgres pg = EmbeddedPostgres.builder().start();
Statement dbSetup = pg.getDatabase("postgres", "postgres").getConnection().createStatement();
dbSetup.execute("create database portal;");
dbSetup.execute("CREATE USER testadmin WITH PASSWORD 'testadmin123' CREATEDB;");
dbSetup.execute("ALTER USER testadmin WITH SUPERUSER;");
dbSetup.execute("ALTER ROLE testadmin SUPERUSER;");

String procedure = "CREATE PROCEDURE insert_data(a integer, b integer)\n" +
                   "LANGUAGE SQL\n" +
                   "AS $$\n" +
                  "INSERT INTO tbl VALUES (a);\n" +
                  "INSERT INTO tbl VALUES (b);\n" +
                  "$$;";

ResultSet rsnew = pg.getDatabase("testadmin", "portal").getConnection().createStatement().executeQuery(procedure);

Facing the below Exception

org.postgresql.util.PSQLException: ERROR: syntax error at or near "PROCEDURE"
  Position: 8
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
	at com.myproject.embeddedpostgres.TestPostgreSQL.main(TestPostgreSQL.java:62)

Can anyone please help.

MohakWadhwani commented

@atul-girishkumar-E3574 did it got resolved facing similar issue

atulg-fw commented

@atul-girishkumar-E3574 did it got resolved facing similar issue
No.
Procedure was implemented in PostgresSQL 11. But the Postgres version used in the otj-pg-embedded library is 10

  • 👍1
MohakWadhwani commented

@atul-girishkumar-E3574
this can be resolved by EmbeddedPostgres.builder().setPgBinaryResolver(new CustomPostgresBinaryResolver()).start();
you need to write your resolver.

jktu2870 commented

in my case updating PostgreSQL to version 12 solved the problem

mikebell90 commented

As noted we've released 1.0.0 which is testcontainers based and uses PG 13 by default (can be customized!). We expect this resolves the issue, and while PRs are welcome against the legacy branch, we are not planning on formally maintaining pre-1.xx code base.