PGPOOL: unnamed prepared statement does not exist  

Gurjeet_S wrote:
As I suspected in the forum post, pgpool is sending queries of its own between the application's PREPARE and BIND calls.

At both the instances in your log where you see this error, 'unnamed prepared statement does not exist', you can see a few lines above it that the application had parsed a query:

duration: 0.693 ms parse <unnamed>: SELECT

then pgpool sends its queries:

duration: 0.193 ms statement: SELECT count(*) FROM pg_class ...
...

And then we see application's bind message and the error:

ERROR: 26000: unnamed prepared statement does not exist
LOCATION: exec_bind_message, postgres.c:1444


On a PARSE (aka PREPARE) message Postgres creates an unnamed portal, and it drops this unnamed portal as son as it sees another query that does not use PARSE message.

By design, pgpool asks postgres database for the metadata of the tables involved in the query. Here pgpool is sending its metadata queries immediately after the PARSE message, hence causing Postgres to drop the just created unnamed portal. And when the application comes around to perform BIND operation on that unnamed statement, Postgres correctly says that there is no unnamed portal/statement in effect, because it has been dropped.

If you choose to deploy PPAS 9.1 clusters, (or in the next iteration of PG/PPAS 9.0 servers) then you should get pgpool 3.1 which alleviates the problem somewhat since in that version of pgpool, the cache size for table metadata has been increased from 32 to 128. You may still see this error, but not until you use more than 128 tables in your sessions.

Other, and a surefire, way to work around this would be to configure your application (or the ORM framework) to either not use unnamed PREPARE statements, or ti use named PREPARE statements.

Hope it clearly explains why you are seeing the problem.

mbaird wrote:
Gurjeet, thank you so much for looking at this. This issue makes sense to me now. I'm not sure how I should proceed though.

I'm currently using 64bit Postgres 9.1 open source. Are you saying that if I used Postgres Plus Advanced Server 9.1 that it would come with the newer version of PGPool? Is there any ETA on the open source version getting upgraded to the newer version of PGPool?

Also, I'm confused about the price difference of the open source Postgres versus Postgres Plus Advanced Server Cloud DB clusters. Are they the same price? Do I have to buy some extra PPAS licenses or something to use that version of Cloud DB?

I'd rather fix my app to always use named PREPARE statements, but I've just spent about an hour searching for a way to force Hibernate to use named PREPARE statements and I haven't found anything. Are you guys aware of a way to force Hibernate to name all prepare statements? The more I read the more I think this might actually be a JDBC thing that I have no control over, so I'm not sure there is a application side fix for this.

Gurjeet_S wrote:
Yes, Advanced Server 9.1 instances have the newer version of pgpool, which may solve your problem since it increases the metadata cache from 32 to 128. But if your application uses more than that many tables in a single session, then you will hit this error again.

Although the price is the same for the open source PostgreSQL and the Advanced Server version of cluster, I'd let Gary Carter answer this one and how they may differ in the future.

Typically, I have seen ORM frameworks PREPARE a statement for single use. That is, they perform PREPARE-BIND-EXECUTE-DISCARD for every statement they come across. Instead of helping improve performance (original intention of PREPARE-once EXECUTE-multiple-times pattern), this pattern actually hampers perfornance since the application is now doing 3 network round-trips for the same task that could be done in one network round-trip if it were to use unprepared statements.

So, if your application is performing the same PREPARE-BIND-EXECUTE-DISCARD pattern, then it might help in performance and getting rid of this error if you configure it to not use prepared statements at all.

Regards,

mbaird wrote:
Actually in reading up on this some more I've found that any parametrized query in JDBC, since JDBC version 3.0, seems to send these 3 prepare/bind/execute commands. Hibernate caches the prepared statement, so performance shouldn't be a big issue, but there is absolutely no way for me to remove these <unnamed> statements from my app. This is just how the Java JDBC driver for Postgres works.

What bothers me is that I've run my app against PGPool before without this issue. I don't understand why PGPool needs to be querying for table metadata if all it is being used for is a load balancer. Is there some PGPool configuration option that could turn off those metadata queries?