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

[Láznička Vladimír]

Hello,

Thank you for your reply. I did not realize this might be the reason... I guess we just have to be careful when using the pgAdmin. We are also currently in the process of switching the replication mode to MASTER/SLAVE, which seems to be far less restricted, and as far as I tested, it doesn't have a problem in this situation - DML is sent to the PRIMARY node (as stated in the docs), which in turn replicates the change to the SLAVE node.

In any case - I consider this issue solved, thank you again for your time.

With best regards,
Vladimír Láznička

-----Original Message-----
From: Bo Peng <pengbo at sraoss.co.jp> 
Sent: Thursday, January 21, 2021 6:23 AM
To: Láznička Vladimír <Vladimir.Laznicka at cca.cz>
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 7391] Odd behavior of DML commands from pgAdmin with Native Replication and Load Balancing

Hi,

Thank you for sharing the logs.

It seems if you specify queries in pgAdmin like:

  SELECT * FROM <table> WHERE <primary_key> = <value>;
  DELETE FROM <table> WHERE <primary_key> = <value>;

the queries above are executed as multi-statement queries.

Pgpool-II doesn't support multi-statement queries.
You need to send statements one by one.

[Restrictions]
https://www.pgpool.net/docs/37/en/html/restrictions.html

On Wed, 20 Jan 2021 10:01:24 +0000
Láznička Vladimír <Vladimir.Laznicka at cca.cz> wrote:

> Hello,
> 
> Sorry for my late reply, I had some issue with logging from pgpool to SYSLOG, where certain messages, like those denoting which queries where sent to which node from my test session... I don't know if the message spam was too severe, so SYSLOG decided to just not process some of them, but in the end I limited the number of users that connected to pgpool by editing the pool_hba.conf file and the expected messages finally appeared. Anyway...
> 
> In the attachment are the messages from the pgpool log that describe the activity of my test session (it has PID 6454), which was load balanced to node 0. I tried few queries to see, what will appear in the log:
> 
> 1) I ran the aforementioned combination SELECT and DELETE command - it was sent as one command to DB node 0 only.
> 2) Then I ran a SELECT on the same table as in the previous command to list remaining rows. It created pretty heavy message spam from pgAdmin, issuing SELECT commands on views such as pg_class and pg_attribute, with another session (with PID 6444) issuing one SELECT of its own.
> 3) Then I ran another SELECT on the same table, but limiting the results by searching for specific record by WHERE clause. It created the same heavy message spam from pgAdmin as before.
> 4) As the last test I ran a DELETE on the record my SELECT returned earlier. Here you can see it correctly sent the DELETE to both DB nodes.
> 
> I hope the log messages help you figure out what might be happening. Let me know if you I should try anything else.
> 
> Thank you for your help in the matter.
> 
> Vladimír Láznička
> 
> -----Original Message-----
> From: Bo Peng <pengbo at sraoss.co.jp>
> Sent: Friday, January 15, 2021 9:38 AM
> To: Láznička Vladimír <Vladimir.Laznicka at cca.cz>
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 7391] Odd behavior of DML commands from 
> pgAdmin with Native Replication and Load Balancing
> 
> Hi,
> 
> 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


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