[Pgpool-general] Read-only explicit transactions

[Toshihiro Kitagawa]

On Mon, 18 Jul 2011 14:45:10 -0700
Josh Berkus <josh at agliodbs.com> wrote:

> Tatsuo, all:
> 
> I'm setting up a system where I will have read-only explicit
> transactions (that is, multi-statement read-only transactions) hitting
> pgpool (this is common with Python applications).
> 
> If I'm doing Master-Standby replication on the backend, how do I tell
> pgpool to load-balance the requests despite them having a BEGIN statement?

pgpool determines whether to load-balance or not in the first sentence of
multi-statement. So if "BEGIN" is the first sentence of multi-statement,
pgpool can not load-balance it.

For example, pgpool can load-balance the request if it's the following case:

BEGIN;
SELECT ...; SELECT ...; SELECT ...;
COMMIT;

pgpool-II 3.0 or later(Master/Slave mode) load-balances queries in
explicit transactions without special setting.

However, note that multi-statement must not include write-queries.
And, each "SELECT" query is sent to the same backend node,
because pgpool decides a load balancing node in every session.

-- 
Toshihiro Kitagawa
SRA OSS, Inc. Japan