[pgpool-general: 873] Re: JDBC application cannot connect to pgpool

Karsten Düsterloh pgpool-general-ml at tal.de
Tue Aug 7 15:31:31 JST 2012


Hi!

I think we experience the same problem.

Soni Maula Harriz wrote:
> we are using pgpool-II 3.2.0 with replication mode and load balancing
> OS : Centos 6 (Pgpool and postgresql 8.4.9), Ubuntu (Postgresql 8.4.11)

We're using pgpool-II 3.2.0 with replication mode and load balancing
under Debian 5 with Postgresql 8.3.16 servers as backends.
(We were using pgpool-II 2.3.3 before.)

> application using JDBC 4
> we have a problem : the application cannot connect to the database

We're using the Jabber server "openfire", which is written in Java and
indeed using JDBC (and proxool) for its database connection.

> replication_mode = on
> insert_lock = on
> replicate_select = off
> insert_lock = on
> replication_stop_on_mismatch = on
> failover_if_affected_tuples_mismatch = on
> load_balance_mode = on
> ignore_leading_white_space = on
> white_function_list = ''
> black_function_list = 'nextval,setval'
> fail_over_on_backend_error = on

Same here, remainder is usually set to default values.

We can log into the database using psql with the openfire database
credentials just fine, but openfire can't.

I've logged two startups of openfire in *postgresql*, one against pgpool
and failing and one against one the postgresql node directly and working.

Failure via pgpool:
> 2012-08-07 08:08:01.798 CEST [17977] LOG:  connection received: host=xxx port=46636
> 2012-08-07 08:08:01.798 CEST [17978] LOG:  connection received: host=xxx port=46637
> 2012-08-07 08:08:01.798 CEST [17977] LOG:  connection authorized: user=jabber database=jabber
> 2012-08-07 08:08:01.798 CEST [17978] LOG:  connection authorized: user=jabber database=jabber
> 2012-08-07 08:08:01.833 CEST [17978] LOG:  duration: 0.183 ms  parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
> 2012-08-07 08:08:01.834 CEST [17978] LOG:  disconnection: session time: 0:00:00.035 user=jabber database=jabber host=xxx port=46637
> 2012-08-07 08:08:01.841 CEST [17977] LOG:  duration: 0.294 ms  parse <unnamed>: SELECT 1
> 2012-08-07 08:08:01.843 CEST [17977] LOG:  disconnection: session time: 0:00:00.045 user=jabber database=jabber host=xxx port=46636
(Plus like 90 more tries of the same structure before openfire gives up.)


Working using postgresql directly:
> 2012-08-07 07:56:21.873 CEST [15940] LOG:  connection received: host=yyy port=36270
> 2012-08-07 07:56:21.873 CEST [15941] LOG:  connection received: host=yyy port=36271
> 2012-08-07 07:56:21.876 CEST [15941] LOG:  connection authorized: user=jabber database=jabber
> 2012-08-07 07:56:21.876 CEST [15940] LOG:  connection authorized: user=jabber database=jabber
> 2012-08-07 07:56:21.910 CEST [15940] LOG:  duration: 0.206 ms  parse <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
> 2012-08-07 07:56:21.910 CEST [15940] LOG:  duration: 0.022 ms  bind <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
> 2012-08-07 07:56:21.910 CEST [15940] LOG:  duration: 0.042 ms  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
> 2012-08-07 07:56:21.924 CEST [15940] LOG:  duration: 0.071 ms  parse <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
> 2012-08-07 07:56:21.924 CEST [15940] LOG:  duration: 0.007 ms  bind <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
> 2012-08-07 07:56:21.924 CEST [15940] LOG:  duration: 0.009 ms  execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
> 2012-08-07 07:56:21.928 CEST [15941] LOG:  duration: 0.257 ms  parse <unnamed>: SELECT 1
> 2012-08-07 07:56:21.928 CEST [15941] LOG:  duration: 0.052 ms  bind <unnamed>: SELECT 1
> 2012-08-07 07:56:21.928 CEST [15941] LOG:  duration: 0.014 ms  execute <unnamed>: SELECT 1
(etc. until minimum number of parallel connections are open, then normal
proceedings to read Jabber users, and so on.)


It seems as if pgpool does not pass through bind and execute steps of
prepared statements anymore?


Regards,

Karsten Düsterloh


More information about the pgpool-general mailing list