12

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.

Mark B
  • 183,023
  • 24
  • 297
  • 295
  • @Zecas Yeah I kind of know how the site works, you don't need to explain that to me. I still don't have a solution that allows me to use the latest version of JDBC against PGPool II. As far as I'm concerned this is still an open question. – Mark B May 25 '12 at 12:58
  • You might want to make sure your queries are running inside of a transaction. The prepared statement is going to get discarded if another transaction comes along on the same connection. By default, the postgresql jdbc driver doesn't use transactions, which seems a bit odd to me. You can set the transactional behaviour in persistence.xml if I recall. – PlexQ Dec 08 '12 at 11:35
  • Why are you using PgPool? Can't you use DBCP, BoneCP or c3p0 to directly manage the connection pool from within your app? – Chochos Feb 20 '13 at 16:01
  • @Chochos I'm not using it as a connection pool. I'm using it as a load balancer across multiple instances of the DB. – Mark B Feb 22 '13 at 16:10
  • mbaird, could you include your workaround as an answer Might as well close this question out. I'd be happy to upvote. – Chris Travers Mar 14 '13 at 09:12

2 Answers2

3

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.

Mark B
  • 183,023
  • 24
  • 297
  • 295
  • 3
    Another option to avoid server side prepared statements would be to use the parameter `prepareThreshold` with a really high number of executions. –  Mar 15 '13 at 16:19
-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...

dataj1998
  • 11
  • 3
  • I'm just using JPA queries. Hibernate is then creating prepared statements and sending them to the JDBC driver. However, from what I've read about the PostgreSQL JDBC driver, there is no way to do parameterized statements without using Prepared Statements. And let's face it, pretty much all queries have parameters. And I can't name the prepared statements since I'm not the one creating them, Hibernate is. – Mark B Mar 22 '12 at 13:08