[pgpool-general: 1612] PgPool 3.2.3 problem with master/slave replication, load_balance, and SET TRANSACTION ISOLATION LEVEL READ COMMITTED

[José Pedro]

Hi,

I'm trying to use pgpool2 to load-balance an application over which I
don't have much control. This application encloses its SQL statements
in:

BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
...
several selects...
...
COMMIT;

The problem I have is that the "SET TRANSACTION ISOLATION LEVEL READ
COMMITTED;" statement apparently turns off the load balancing.

Pgpool works well (sends the SELECT 1 to node id 1, which is the slave) when:
- No transaction (at 17:48:58)
- Inside begin / commit block (at 17:49:18)
- Inside begin transaction isolation level read committed / commit
block (at 17:49:50)

However, if the client sends "BEGIN; SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;", pgpool sends the simple select to node 0, the master
(at 17:50:04).

This looks like it's a bug, because 1) the documentation only forbids
load-balancing with SERIALIZABLE transactions; and 2) the default
transaction isolation level itself is READ COMMITTED (at 17:50:36)!

Am I doing anything wrong? Would you like to see the pgpool.conf file?

You can find the psql session below, intermixed with the corresponding
pgpool log lines.

Ubuntu 12.04 x64, Postgres 9.2.4 from PGDG repo, and PgPool-II 3.2.3
compiled from sources.

Thanks,
-José Pedro


$ psql ...
psql (9.2.4)
Type "help" for help.

DBNAME=> select 1;
2013-04-11 17:48:58 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: select 1;
DBNAME=> begin;
BEGIN
2013-04-11 17:49:15 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: begin;
2013-04-11 17:49:15 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: begin;
DBNAME=> select 1;
2013-04-11 17:49:18 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: select 1;
DBNAME=> commit;
COMMIT
2013-04-11 17:49:22 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: commit;
2013-04-11 17:49:22 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: commit;
DBNAME=> select 1;
2013-04-11 17:49:24 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: select 1;
DBNAME=> begin transaction isolation level read committed;
BEGIN
2013-04-11 17:49:48 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: begin transaction isolation level read committed;
2013-04-11 17:49:48 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: begin transaction isolation level read committed;
DBNAME=> select 1;
2013-04-11 17:49:50 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: select 1;
DBNAME=> commit;
COMMIT
2013-04-11 17:49:54 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: commit;
2013-04-11 17:49:54 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: commit;
DBNAME=> begin; set transaction isolation level read committed;
BEGIN
2013-04-11 17:50:01 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: begin;
2013-04-11 17:50:01 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: begin;
SET
2013-04-11 17:50:01 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: set transaction isolation level read committed;
2013-04-11 17:50:01 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: set transaction isolation level read committed;
DBNAME=> select 1;
2013-04-11 17:50:04 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: select 1;
DBNAME=> commit;
COMMIT
2013-04-11 17:50:10 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: commit;
2013-04-11 17:50:10 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: commit;
DBNAME=> select 1;
2013-04-11 17:50:14 LOG:   pid 17532: DB node id: 1 backend pid: 6166
statement: select 1;
DBNAME=> show default_transaction_isolation ;
2013-04-11 17:50:36 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: show default_transaction_isolation ;
 default_transaction_isolation
-------------------------------
 read committed
(1 row)

DBNAME=> show transaction_isolation ;
2013-04-11 17:50:44 LOG:   pid 17532: DB node id: 0 backend pid: 4930
statement: show transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)

DBNAME=> \q