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

Tatsuo Ishii ishii at postgresql.org
Wed Apr 17 14:56:13 JST 2013


Sorry for delay.

> 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).

Actually this is an expected behavior.

> 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?

The bug is in the doc. It should be stated that if you use "muti
statment query" (multi SQL statements combined with ";" in sigle
line), pgpool does not do the load balance. This is due to the
limitation of pgpool-II in that it does not recognize properly multi
statement queries and sends the query blindly to the master.

Sorry for the incovenience, but we cannot solve the problem until
pgpool-II handles multi statement queries correctly.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list