Java queries against PGPool II cause “unnamed prep

Question

I have a Java app that uses a Postgres database and I'm trying to introduce PGPool in order to scale up my database. I'm running into a problem where Postgres throws the following error: unnamed prepared statement does not exist. After cranking up the logging on Postgres I see the following stuff happening for every select statement my app executes:

EDTLOG:  00000: duration: 7.585 ms  parse <unnamed>: "my select statement here"
EDTLOG:  00000: duration: 0.088 ms  bind <unnamed>: "my select statement here"
EDTLOG:  00000: duration: 79.014 ms  execute <unnamed>: "my select statement here"

But sometimes, between the parse/bind/execute steps PGPool will execute some extra queries, so that the log looks like this:

EDTLOG:  00000: duration: 7.585 ms  parse <unnamed>: "my select statement here"
EDTLOG:  00000: duration: 0.088 ms  bind <unnamed>: "my select statement here"
EDTLOG:  00000: duration: 0.328 ms  statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 'my_table' AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
EDTLOG:  00000: duration: 79.014 ms  execute <unnamed>: "my select statement here"
EDTERROR:  26000: unnamed prepared statement does not exist
EDTLOG:  00000: duration: 0.022 ms  parse S_2: ROLLBACK
EDTLOG:  00000: duration: 0.005 ms  bind S_2: ROLLBACK
EDTLOG:  00000: duration: 0.008 ms  execute S_2: ROLLBACK

From what I understand, because the query is unnamed it gets thrown away by the Postgres if another query comes in during that database session before the unnamed query is executed. So because PGPool is sometimes issuing these extra queries between the parse/bind/execute steps it's causing the query to be thrown away.

My first thought was that perhaps my Java app didn't need to be sending parse/bind/execute statements for every query. But it looks like this is the default behavior for the Postgres JDBC driver ever since JDBC version 3 and Postgres 7.4 http://jdbc.postgresql.org/documentation/head/server-prepare.html. I suppose I could try completely disabling server side prepared statements, but the documentation doesn't specify how to do that, and I'm not sure that's something I want to do anyway.

My second thought was to get PGPool II to stop sending those meta data queries. Since I'm just trying to use PGPool as a load balancer I don't really see why it needs to know all about my table metadata. I tracked down the code that is executing those queries in the is_system_catalog method of the PGPool source here: https://github.com/iakio/pgpool-II/blob/master/pool_select_walker.c#L256 It seems that PGPool wants to know about my table relationships for some reason, and unfortunately I'm not seeing any way to disable that behavior.

Any insight on how to work around this issue would be greatly appreciated.

Some info about my environment:

JDBC Driver: postgresql-9.1-901.jdbc4.jar
Java version "1.6.0_31"
Spring 3.1 managed JPA 
Hibernate 3.5
Postgres 9.1

UPDATE: I found a workaround to the issue. By placing protocolVersion=2 in the JDBC URL it basically tells the Postgres JDBC driver not to use server side prepared statements. This allows my app to run while using PGPool II in front of my database. I'm bothered by the fact that I have to fall back to the JDBC version 2 protocol just to use PGPool though.

Answer 1

What is, if you name your prepared statement?

And another question arises: Why do you use prepared statements, if you don't need them?

You can easily make "normal" statements against the jdbc driver...

Answer 2

I found a workaround to the issue. By placing protocolVersion=2 in the JDBC URL it basically tells the Postgres JDBC driver not to use server side prepared statements. This allows my app to run while using PGPool II in front of my database. I'm bothered by the fact that I have to fall back to the JDBC version 2 protocol just to use PGPool though.