I have a pgpool 3.5.4 with memcache enabled, and I use it to connect to Redshift.
I wrote two simple programs, one in Java (JDBC postgresql-9.4.1212.jre6.jar) and another one in Python (using psycopg2 postgres package) that just connects to pgpool, and execute a simple query (eg: select * from customer limit 10;) and I've noticed strange and different behaviors. I also ran the queries using the command line tool psql.
1) Using JDBC with pgpool with caching enabled I get an error
2016-11-15 10:56:27: pid 31043: FATAL: Backend throw an error message
2016-11-15 10:56:27: pid 31043: DETAIL: Exiting current session because of an error from backend
2016-11-15 10:56:27: pid 31043: HINT: BACKEND Error: "portal "pgpool31043" does not exist"
2) Using JDBC with pgpool with caching disabled it works
3) Using psycopg2 or psql command line with pgpool with caching either enabled or disabled it works
Can someone help me understand why only JDBC is not working?
There are two protocols JDBC uses to communicate, simple query protocol and extended query protocol.
pgpool II however, doesn't work very well with extended query protocol.
In the documentation of pgjdbc driver in github (https://github.com/pgjdbc/pgjdbc) there is a parameter named preferQueryMode. To fix this issue, just set the preferQueryMode to simple, and the problem will just go away.
I tested this set up with two customers so far, using pgpool in front of postgres and redshift, and it worked perfectly.