Hi,
> The source code for the PostgreSQLValidConnectionChecker class includes the
> following validity check:
>
> try
> {
> stmt = cn.createStatement();
> stmt.execute("");
> }
> // etc..
>
> This empty-string query is exactly what's causing the problem. When I
> disable the connection checker by commenting out the XML element shown above,
> everything works correctly.
I committed the patch to avoid this problem.
Load balancing will work correctly with next releases(3.0.5, 3.1.0)
even if JBoss sends empty-queries.
--
Toshihiro Kitagawa
SRA OSS, Inc. Japan
On Fri, 22 Jul 2011 02:37:55 -0400
Matt Solnit <[email protected]> wrote:
> On Jul 21, 2011, at 11:04 PM, Matthew Solnit wrote:
>
> > On Jul 20, 2011, at 11:16 PM, Toshihiro Kitagawa wrote:
> >
> >> On Wed, 20 Jul 2011 11:58:45 -0400
> >> Matt Solnit <[email protected]> wrote:
> >>
> >>> Hi everyone. I'm trying to understand why load balancing doesn't work
> >>> with the PostgreSQL JDBC driver, and what (if anything) can be done about
> >>> it. I have to admit, I find the documentation a bit confusing. Here is
> >>> what it currently says at
> >>> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html:
> >>>
> >>> "Note: the JDBC driver has an autocommit option. If autocommit is
> >>> false, the JDBC driver sends "BEGIN" and "COMMIT" by itself. So pgpool
> >>> cannot do any load balancing. You need to call setAutoCommit(true) to
> >>> enable autocommit."
> >>
> >> I think we have to revise the manual more clearly...
> >>
> >> It's a description about load balancing in replication mode.
> >> In the case of Master/Slave mode, please watch "Streaming Replication"
> >> section:
> >>
> >> In an explicit transaction:
> >>
> >> - Transaction starting commands such as BEGIN are sent to the primary
> >> node.
> >> - Following SELECT and some other queries that can be sent to both
> >> primary or standby are executed in the transaction or on the standby
> >> node.
> >> - Commands which cannot be executed on the standby such as INSERT are
> >> sent to the primary. After one of these commands, even SELECTs are
> >> sent to the primary node, This is because these SELECTs might want
> >> to see the result of an INSERT immediately. This behavior continues
> >> until the transaction closes or aborts.
> >>
> >>> And yet, a recent post to this mailing list
> >>> (http://pgfoundry.org/pipermail/pgpool-general/2011-July/003819.html)
> >>> contradicts this, and says that recent versions of pgpool-II can handle
> >>> explicit transactions. And the flow chart
> >>> (http://pgpool.projects.postgresql.org/pgpool-II/doc/where_to_send_queries.pdf)
> >>> seems to say the same thing.
> >>>
> >>> Even more confusing is the fact that it *does* seem to work once in a
> >>> while. I'm currently using pgpool-II 3.0.4, with PostgreSQL 9.0.2 in
> >>> streaming replication mode, and JDBC driver. When I enable statement
> >>> logging in both pgpool-II and PostgreSQL, I can see that a very small
> >>> number queries do go to the slave. I have not been able to figure out
> >>> any pattern to this.
> >>>
> >>> I would really love to understand more about what is going on. Any help
> >>> would be sincerely appreciated.
> >>
> >> I guess that the last condition mentioned above is related.
> >>
> >> For example:
> >>
> >> BEGIN
> >> SELECT -> load balancing
> >> INSERT etc.(write-query)
> >> SELECT -> not load balancing
> >> SELECT -> not load balancing
> >> COMMIT
> >>
> >> --
> >> Toshihiro Kitagawa
> >> SRA OSS, Inc. Japan
> >
> >
> > Thank you very much for the detailed response. I'm starting to dig into
> > this more, and here's what I'm seeing:
> > 1. Using the JDBC driver with no connection pool (i.e. using
> > java.sql.DriverManager), everything seems to work as expected.
> > 2. Using the c3p0 JDBC connection pool inside Tomcat, everything seems to
> > work as expected.
> > 3. Using the JBoss connection pool inside JBoss 4.2.2, things do *not*
> > work as expected.
> >
> > It just so happens that up until now, I've been using case 3 exclusively.
> > So I thought that there was a fundamental issue with JDBC and pgpool-II
> > load balancing, but in fact it seems that it's isolated to the JBoss
> > connection pool.
> >
> > Specifically, what I'm seeing is the message "Parse: Unable to parse the
> > query:" show up inside the pgpool-II log file. Once this shows up, all
> > queries appear to go to the master. I'm still trying to narrow down what
> > the specific relationship is between the "Unable to parse the query"
> > message and load balancing, but there definitely appears to something going
> > on here.
> >
> > I'm also trying to figure out what it is about the JBoss connection pool
> > that makes the "Unable to parse the query" happen in the first place.
> >
> > Any comments would be appreciated :-).
> >
> > -- Matt
> >
> > P.S. It seems like any query using a system table (e.g. pg_class,
> > pg_catalog, etc.) goes to the master, no matter what. I've found this to
> > be true even when using a "regular" client like psql.
>
> Found the problem. It's in the PostgreSQL "valid connection checker" class
> provided by JBoss. A typical JBoss data source configuration file for
> PostgreSQL
> includes the following XML:
>
>
> <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.PostgreSQLValidConnectionChecker</valid-connection-checker-class-name>
>
> The source code for the PostgreSQLValidConnectionChecker class includes the
> following validity check:
>
> try
> {
> stmt = cn.createStatement();
> stmt.execute("");
> }
> // etc..
>
> This empty-string query is exactly what's causing the problem. When I
> disable the connection checker by commenting out the XML element shown above,
> everything works correctly.
>
> Apparently, this JBoss behavior is intentional, for performance reasons that
> came from discussing with PostgreSQL developers. See
> https://issues.jboss.org/browse/JBAS-3133 and
> http://community.jboss.org/message/298853#298853 for details.
>
> At any rate, I *strongly* recommend including this information in the
> pgpool-II documentation. The current version of JBoss (AS 7) still has this
> code (see
> https://source.jboss.org/browse/JBossAS/connector/src/main/java/org/jboss/as/connector/adapters/jdbc/extensions/postgres/PostgreSQLValidConnectionChecker.java?r=6c64725de60a324628dc648baae080a9a7516dca).
>
> Hope this is helpful,
> Matt
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
