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

[Jesús Campoy]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20221006/008ed2fd/attachment.htm>