[pgpool-general: 8448] Re: Problem with load balancing

[Bo Peng]

Hello,

> > 2022-09-29 10:59:05.544:  test_app   pid 441: LOG:  DB node id: 0 backend
> > pid: 525 statement: BEGIN;DELETE FROM scenario WHERE  Id =-1 and Role = 3
> >
> > 2022-09-29 10:59:05.546:  test_app   pid 441: LOG:  statement: SAVEPOINT
> > _EXEC_SVP_0xa3ff298
> >
> > 2022-09-29 10:59:05.546:  test_app   pid 441: LOG:  DB node id: 0 backend
> > pid: 525 statement: SAVEPOINT _EXEC_SVP_0xa3ff298
> >
> > 2022-09-29 10:59:05.546:  test_app   pid 441: LOG:  DB node id: 1 backend
> > pid: 15985 statement: SAVEPOINT _EXEC_SVP_0xa3ff298
> >
> > 2022-09-29 10:59:05.547:  test_app   pid 441: LOG:  pool_send_and_wait:
> > Error or notice message from backend: : DB node id: 1 backend pid: 15985
> > statement: "SAVEPOINT _EXEC_SVP_0xa3ff298" message: "SAVEPOINT can only be
> > used in transaction blocks"

SAVEPOINT can only be established when inside a transaction block.
Because the previous statement "BEGIN" wasn't sent to node1, this error occurs on node1.

Pgpool-II can't handle multi-statement correctly.
Because of this restriction, "BEGIN" wasn't sent to node1.
To avoid this error, please be sure to send the statements separately.

[Restrictions]
https://www.pgpool.net/docs/latest/en/html/restrictions.html
---------------------
Load Balancing

Multi-statement queries (multiple SQL commands on single line) are always sent to primary node (in streaming replication mode) or main node (in other modes). Usually Pgpool-II dispatch query to appropriate node, but it's not applied to multi-statement queries. 
---------------------

On Thu, 6 Oct 2022 17:24:13 +0200
Jesús Campoy <jesuscampoy at gmail.com> wrote:

> I forget to indicate the pgpool version: 4.3.2
> 
> El jue, 6 oct 2022 9:25, Jesús Campoy <jesuscampoy at gmail.com> escribió:
> 
> > Hi,
> >
> >
> >
> > I’m writing this email because I have problems with load balance. It would
> > be great if someone could help me with this issue.
> >
> >
> >
> > My environment is:
> >
> >
> >
> >    - OS: RHEL 5.7
> >    - Postgres 11
> >    - streaming replication as redundancy mode
> >    - In one server runs pgpool and the primary database (node 0), in
> >    another server runs the standby database (node 1).
> >    - Read Commited as default transaction isolation configured in Postgres
> >
> >
> >
> > Redundancy and load balancing is working in most of the cases as expected
> > and replication delay is 0.
> >
> >
> >
> > However, some transactions are not working and pgpool shows the following
> > log:
> >
> >
> >
> > 2022-09-29 10:59:05.544:  test_app   pid 441: LOG:  DB node id: 0 backend
> > pid: 525 statement: BEGIN;DELETE FROM scenario WHERE  Id =-1 and Role = 3
> >
> > 2022-09-29 10:59:05.546:  test_app   pid 441: LOG:  statement: SAVEPOINT
> > _EXEC_SVP_0xa3ff298
> >
> > 2022-09-29 10:59:05.546:  test_app   pid 441: LOG:  DB node id: 0 backend
> > pid: 525 statement: SAVEPOINT _EXEC_SVP_0xa3ff298
> >
> > 2022-09-29 10:59:05.546:  test_app   pid 441: LOG:  DB node id: 1 backend
> > pid: 15985 statement: SAVEPOINT _EXEC_SVP_0xa3ff298
> >
> > 2022-09-29 10:59:05.547:  test_app   pid 441: LOG:  pool_send_and_wait:
> > Error or notice message from backend: : DB node id: 1 backend pid: 15985
> > statement: "SAVEPOINT _EXEC_SVP_0xa3ff298" message: "SAVEPOINT can only be
> > used in transaction blocks"
> >
> > 2022-09-29 10:59:05.547:  test_app   pid 441: WARNING:  packet kind of
> > backend 1 ['E'] does not match with main/majority nodes packet kind ['C']
> >
> > 2022-09-29 10:59:05.547:  test_app   pid 441: FATAL:  failed to read kind
> > from backend
> >
> > 2022-09-29 10:59:05.547:  test_app   pid 441: DETAIL:  kind mismatch
> > among backends. Possible last query was: "SAVEPOINT _EXEC_SVP_0xa3ff298"
> > kind details are: 0[C] 1[E: SAVEPOINT can only be used in transaction
> > blocks]
> >
> > 2022-09-29 10:59:05.547:  test_app   pid 441: HINT:  check data
> > consistency among db nodes
> >
> > 2022-09-29 10:59:05.548: main pid 379: LOG:  child process with pid: 441
> > exits with status 512
> >
> >
> >
> > If I disable the load balance mode, this transaction always works.
> >
> > I also tried without success changing the following parameters:
> >
> > o   disable_load_balance_on_write
> >
> > o   app_name_redirect_preference_list
> >
> > o   black_query_pattern_list
> >
> > o   primary_routing_query_pattern_list
> >
> > o   database_redirect_preference_list
> >
> >
> >
> > Thanks in advance.
> >
> >
> >
> > Best,
> >
> > Jesús
> >


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/