Having trouble using crux-jdbc with postgresql

[coxchen]

Hello

I'm trying to use crux-jdbc with postgresql, but I got some ERROR when staring crux node. The following is part of the stacktrace:

clojure.lang.ExceptionInfo: Error starting system {:k [:crux.jdbc/connection-pool]}
	at crux.system$start_system$fn__16829.invoke(system.clj:217)
        .... (omitted)

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "NOT"
  Position: 17
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at next.jdbc.result_set$stmt__GT_result_set.invokeStatic(result_set.clj:631)
	at next.jdbc.result_set$stmt__GT_result_set.invoke(result_set.clj:626)
	at next.jdbc.result_set$eval20674$fn__20682.invoke(result_set.clj:884)
	at next.jdbc.protocols$eval19582$fn__19613$G__19573__19622.invoke(protocols.clj:33)
	at next.jdbc$execute_BANG_.invokeStatic(jdbc.clj:237)
	at next.jdbc$execute_BANG_.invoke(jdbc.clj:224)
	at crux.jdbc.psql$$reify__21053.setup_schema_BANG_(psql.clj:31)
	at crux.jdbc$__GT_connection_pool.invokeStatic(jdbc.clj:51)
	at crux.jdbc$__GT_connection_pool.invoke(jdbc.clj:39)
	at crux.system$start_system$fn__16829.invoke(system.clj:209)
	... 34 more

Can anyone help to give me some guide to make it work?

Thank you,

• Cox

[jarohen]

Hi @coxchen, thanks for getting in touch 👋

This looks like an issue with the initial schema migration - specifically the CREATE INDEX IF NOT EXISTS ... command. IF NOT EXISTS was introduced in Postgres 9.5 - could you confirm what version you're using?

Cheers,

James

[coxchen]

Hi @jarohen , thanks for your reply.

I'm running Postgresql on my local Ubuntu machine. The installed version is 11+201.pgdg14.04+1 .
However, when I run SELECT version() , it returns PostgreSQL 9.3.22 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit

So, is it actually version 9.3.22, instead of 11?

Thanks,

• Cox

[jarohen]

I suspect this might be the difference between your client and server versions. A Postgres client at v11 will still be able to query a much older Postgres server - it sounds like your server is at v9.3.22 then?

[coxchen]

Hi @jarohen ,

You are right! Silly me. It works after I use newer Postgres server. Thank you for your help.

Best,

• Cox

[jarohen]

You're welcome, glad to hear it's working 😄 🙏