citusdb ERROR: ERROR: cannot execute PREPARE for a distributed query plan

Question

I'm just did the citusdb tutorial. And local using "psql" works fine. But when I try do the same query using native postgres jdbc I get the error:

ERROR: ERROR: cannot execute PREPARE for a distributed query plan
Query = SELECT count(*) FROM customer_reviews

Local works fine,

postgres=# SELECT count(*) FROM customer_reviews;

count

589859 (1 row)

http://www.citusdata.com/downloads

Answer 1

Well I was using RazorSQL query tool using JDBC. But I change to ODBC and now is working.

Answer 2

PgJDBC uses server-side prepared statements, and it looks like this "citusdb" tool does not support them.

Try setting the prepare threshold, so PgJDBC won't try to prepare statements; see setPrepareThreshold. If I recall correctly you can set it with p repareThreshold=0 in the JDBC URL instead, though that'll then affect all connections.

Answer 3

Citing from the Common Errors page on CitusDB's Wiki.

The most common cause for this error is that tools using ODBC/JDBC drivers typically try to use a PREPARE statement by default, which is currently unsupported by CitusDB. This can very easily be resolved by downgrading the JDBC or ODBC protocol version to avoid using PREPARE statements.

For ODBC drivers, depending on the particular driver/tool you are using there is sometimes an option to disable PREPARE statements. Alternatively, you could just revert the protocol to version 7.2 which is nearly always possible.

For JDBC drivers, the exact steps depend a bit more on the particular driver. But, you can almost always set protocolVersion as 2 either in the driver properties or by specifying it in the connection string.

Answer 4

I am also got the same problem with CitusDB with JDBC integration, but there is a solution.

You have give the Protocol Version with connection string, like

connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres?protocolVersion=2");

Presently CitusDB JDBC works with Protocol Version 2 only.

I got the result, hope it will solve your problem.