[pgpool-general: 554] Re: Hibernate and pgpool-II

[David Kerr]

On Tue, May 29, 2012 at 02:03:09PM +0200, Guillaume Lelarge wrote:
- On Tue, 2012-05-29 at 07:49 +0900, Tatsuo Ishii wrote:
- > > On 05/27/2012 10:31 AM, David Kerr wrote:
- > >> On 5/27/2012 10:28 AM, Ludger Overbeck wrote:
- > >>> Hi,
- > >>>
- > >>> I would like to know if anybody has ever used pgpool-II (in
- > >>> Replication
- > >>> Mode or Master/Slave Mode) with Hibernate? Does this cause any
- > >>> problems?
- > >>>
- > >>> As far as I know in Hibernate SQL-statements (which are sent to the
- > >>> database) are always issued in an explicit transaction. And as the FAQ
- > >>> for pgpool says, that would mean that there is no load balancing in
- > >>> replication mode. Is that correct?
- > >>>
- > >>> Instead I am thinking about using the Master/Slave mode with
- > >>> synchronous
- > >>> streaming replication to avoid that selects deliver stale data (as
- > >>> with
- > >>> asynchronous replication). Would this work?
- > >>>
- > >>> Thanks for your replies.
- > >>
- > >> I've started some testing with Hibernate + PgPool II in load balance /
- > >> master server mode with async Streaming Replication and so far the
- > >> results have been positive.
- > >>
- > >> Dave
- > > 
- > > 
- > > Update on this:
- > > 
- > > Even though my test appeared to be load balancing, the log files show
- > > that, in fact, it was not.
- > > 
- > > I suspect it's as you mentioned the explicit transactions in
- > > hibernate. (I'm in Master/Slave mode.)
- > 
- > I don't know what was your test case but I'm sure pgpool-II 3.0 or
- > later can do load balancing in streaming replication mode even in
- > explicit transactions.
- 
- While I agree with you on this, it really depends on his workload. If he
- does mostly write requests in master/slave mode, all the write queries
- must be executed on the master, so most of the queries will happen on
- the master, making it hard to appreciate the load balancing.

Ok a couple of problems poped up during todays test:
May 29 19:09:53 db01 postgres-db[79161]: [10-1] user=tt,db=assessment ERROR:  portal "" does not exist
May 29 19:09:53 db01 postgres-db[69237]: [10-1] user=tt,db=assessment ERROR:  unnamed prepared statement does not exist
May 29 19:10:03 db01 postgres-db2[79365]: [10-1] user=tt,db=assessment ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 5
May 29 19:10:03 db01 postgres-db[79364]: [10-1] user=tt,db=assessment ERROR:  portal "pgpool_error_portal" does not exist

I have some idea about the prepared statements, setting prepareThreshold=0, or protocolVersion=2. is that the reccomended method?
I've set: reset_query_list = 'ABORT; DISCARD ALL'

I'm confused about the two portal "..." does not exist errors. any help there would be good.

I'm on - pgpool-II version 3.1 (hatsuiboshi)

Thanks