[pgpool-general: 7392] Re: Odd behavior of DML commands from pgAdmin with Native Replication and Load Balancing

Bo Peng pengbo at sraoss.co.jp
Fri Jan 15 17:38:28 JST 2021


Thank you for reporting this issue.

Could you enable the following paramaters and run the same queries via pgAdmin again?
Please to share the log file of pgpool.

  log_per_node_statement = on
  log_client_messages = on

On Thu, 14 Jan 2021 11:33:25 +0000
Láznička Vladimír <Vladimir.Laznicka at cca.cz> wrote:

> Hello,
> I have encountered an unexpected behavior of a DML (or DELETE to be exact) being issued from pgAdmin (v4.28) application accessing the database via pgPool configured with session level load balancing and native replication. I am running 2 servers with the pgPool-II service (version 4.1.3) with the Watchdog component and 2 PostgreSQL servers (version 11.9).
> When I create a new "Query Tool" window in pgAdmin, which creates a new session to pgPool/PostgreSQL that has one of the DB nodes set as load-balance node (doesn't matter which, it behaves the same on both), and write a SELECT command and right after it a DELETE command (or I assume any other DML) like so:
> SELECT * FROM <table> WHERE <primary_key> = <value>;
> DELETE FROM <table> WHERE <primary_key> = <value>;
> And then I let it run as a whole, instead of the DELETE command being replicated to both DB nodes, it is performed only on the load-balance node (the one that receives the SELECT command). This causes data inconsistency as the record gets deleted only on that node, while on the other it is still present. If I issue these commands separately, it behaves as expected - SELECT gets load balanced to the chosen DB node and DELETE gets replicated to both nodes.
> I have also tried to turn off the load balancing (load_balance_mode = off), but that didn't solve the issue - the DELETE was automatically sent to the DB node, which was currently flagged as a "MASTER" node, but was not replicated to the other node. What helped, was setting replicate_select = on, which caused all the commands to be performed on both nodes, regardless of the type of the command.
> I have also tried to replicate this behavior with PSQL command line, where I have put the SELECT and the DELETE commands in one line, but both commands were issued correctly (SELECT on the load-balance node and DELETE on both), so it seems to be a pgAdmin specific issue. Now I understand that the pgAdmin is a 3rd party tool here, but I think it's being used widely enough to warrant some further investigation - whether there is some issue in pgPool code/behavior that causes it to treat the DELETE command as a part of the SELECT command (it is considered as a non-write command...) or if it is some "weird" way the pgAdmin parses those commands and sends them to pgPool.
> I am sending my pgpool.conf file as an attachment for further information, if you need me to send or test anything else, let me know.
> Thank you for your time.
> With best regards,
> Vladimír Láznička

Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan

More information about the pgpool-general mailing list