On Thu, 2011-07-21 at 10:41 +0200, David Hartveld wrote: > Op 21-07-11 08:16, Toshihiro Kitagawa schreef: > > On Wed, 20 Jul 2011 11:58:45 -0400 > > Matt Solnit<msol...@soasta.com> 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 > > I was wondering what will happen in the following scenario, when > streaming replication is replayed between SELECT 2 and 3 on the slave, > altering the 'snapshot' view of the database from the users point of > view - this will possibly return inconsistent data, right? > > 1 BEGIN > 2 SELECT -> Load-balance > Replay xlog on slave where next SELECT is executed > 3 SELECT -> Load-balance > 4 COMMIT
Yes, that's a possible issue. While having the possibility to load balance an explicit transaction is interesting, it should be possible to disable this behaviour. We would like to add a parameter that would allow this. But for 3.2 obviously. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com _______________________________________________ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
