[OPEN-ILS-DEV] pgpool-II version issue

[Galen Charlton]

Hi,

We have discovered an issue with pgpool-II 3.0 and later that Evergreen 
systems that use pgpool in conjunction with replication should be aware 
of.  Specifically, pgpool-II 3.0 introduces a new feature to 
load-balance select statements.

However, in a setup that uses slony to replicate from a master database 
to one or more slaves *AND* uses pgpool to load-balance queries between 
the master and the slave, running a select statement in an explicit 
transaction no longer guarantees that that query will run on the master 
database.  The select will run on the master only if the transaction 
includes prior inserts or updates or if the select is decorated with a 
query comment specified by pgpool.

The problem with this is that Evergreen's .authoritative methods 
currently rely on exactly that behavior.  In turn, there is a bunch of 
code, most visibly in the patron self-service features in the OPAC, that 
follow the pattern of doing a circulation or hold transaction, then 
making a separate .authoritative call to fetch the results to refresh 
the display.

Consequently, at present, if you're using pgpool-II to load-balance 
queries between master and slave databases, think twice before upgrading 
pgpool-II beyond version 2.3.3.

To qualify that, there are modes where you *can* safely use pgpool-II 
3.0+, e.g. for connection pooling.

Longer term, I think it's time that we teach Evergreen about replicated 
setups so that .authoritative methods can directly query the master DB 
when needed.

Regards,

Galen
-- 
Galen Charlton
Director of Support and Implementation
Equinox Software, Inc. / Your Library's Guide to Open Source
email:  gmc at esilibrary.com
direct: +1 770-709-5581
cell:   +1 404-984-4366
skype:  gmcharlt
web:    http://www.esilibrary.com/
Supporting Koha and Evergreen: http://koha-community.org & 
http://evergreen-ils.org