A.47. Release 4.3.6

Import psqlscan source code into Pgpool-II to judge multi statement query. (Tatsuo Ishii)

Psqlscan is a module in the PostgreSQL source tree. It is essentially subset of PostgreSQL SQL scanner but it is specialized for detecting the end of each SQL statement. Therefore we can count the number of SQL statements in a query string by using it.

Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2023-February/04291.html Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2023-April/004320.html

Allow to use multiple statements extensively. (Tatsuo Ishii)

This commit tries to eliminate pgpool's long standing limitations regarding multiple statements (multi-statements).

Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2023-February/004287.html

Use schema qualification for internal queries. (Tatsuo Ishii)

Some of objects such as function and cast did not use "pg_catalog." schema qualification. This does not lead to immediate security concern but using the schema qualification is always good practice.

Fix possible dead lock with shared relation cache. (Tatsuo Ishii)

When a user defined function obtains table locking, call to the function using extended query protocol could lead to dead lock. Here's a scenario:

• (1) In session A client sends parse, bind, execute request for the function to pgpool.

• (2) Pgpool-II in session A forwards the request to PostgreSQL.

• (3) PostgreSQL in session A performs execute, resulting in a table lock.

• (4) In session B client sends parse, bind, execute request for the function to pgpool.

• (5) Pgpool in session B forwards the request to PostgreSQL.

• (6) PostgreSQL in session B performs bind but the table was already locked by PostgreSQL in session A, and it waits for release of the lock.

• (7) After pgpool in session B forwards the execute to PostgreSQL, it acquires semaphore to search shared relation cache to check the volatility of the function. Then it calls do_query and sends flush message to PostgreSQL to obtain the response from PostgreSQL up to this point. But since PostgreSQL is waiting for table lock in #6, pgpool has to wait for messages beyond bind complete.

• (8) After pgpool in session A forwards the execute to PostgreSQL, it tries to acquire semaphore to search shared relation cache to check the volatility of the function but the semaphore was already acquired by pgpool in session B, it waits for the release of the semaphore.

• (9) Session A and session B wait for each other, resulting in a dead lock.

To fix this, modify pool_search_relcache() so that it releases semaphore before calling do_query() (but acquires semaphore after do_query()). By this, session A in #8 above can get semaphore, and go forward. It receives sync message from client and forwards it to PostgreSQL. Upon receiving sync, the user defined function will finish the execution and releases the table lock. This makes PostgreSQL in session B move forward because now it can obtain the table lock.

Fix prefer_lower_delay_standby bug. (bug 798) (Tatsuo Ishii)

When client connects to pgpool, one of standbys are chosen as the load balancing node. If standby delay exceeds delay_threshold while the session continues, prefer_lower_delay_standby will choose the least delay standby node as the new load balancing node and set the target backend to the node. Unfortunately the decision was made before the checking that SELECT query includes writing function etc., Pgpool-II happily sends SELECT which cannot be executed on standby. To fix this, prefer_lower_delay_standby treatment is moved after the writing function etc. check.

Also 033.prefer_lower_standby_delay regression test is modified to include the case above.

Fix compile error on systems using musl libc. (bug 790) (Tatsuo Ishii)

Patch provided by leimaohui.

Fix multiple query cache bug. (Tatsuo Ishii)

Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2023-January/004259.html

Fix sr worker to not send wrong query to standby server in corner case. (Tatsuo Ishii)

When ALWAYS_PRIMARY flag is set, PRIMARY_NODE_ID macro returns node id, rather than -1 even if the primary is down. In this case streaming replication delay check worker sends SELECT pg_current_wal_lsn() or SELECT pg_current_xlog_location() depending on PostgreSQL's version to standby which of course raises an error.

Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2023-February/004279.html

Fix kind mismatch error with DEALLOCATE. (bug 780) (Tatsuo Ishii)

Kind mismatch error occurs when conditions below are all met:

• streaming replication mode

• load balance node is other than primary

• PREPARE is used in a multi-statement query

Fix compile error of regression test (Takuma Hoshiai)

The error occurred by timestamp test using gcc10.

Fix that show pool_version shows row description twice. (Tatsuo Ishii)

Add explanation for wd_priority. (Chen Ningwei)

Add restriction regarding PREPARE/EXECUTE/DEALLOCATE. (Tatsuo Ishii)

Add caution to use -D option with pgpool. (Tatsuo Ishii)

Enhancing "Installation from RPM" section. (Bo Peng)

Add "Backing up PostgreSQL database" section to "Server Setup and Operation" chapter. (Tatsuo Ishii)

Remove the configuration of "-D" start OPTS from "8.2. Pgpool-II + Watchdog Setup Example". (Bo Peng)

Fix restrictions section. (Tatsuo Ishii)

• Add mention about SCRAM-SHA-256 authentication

• Add mention about snapshot isolation mode

Add explanation when pg node status is shown as "unknown". (Tatsuo Ishii)

Mention that the target node to promote must be up and running when executing pcp_promote_node. (Tatsuo Ishii)

Mention that AES256 support requires --with-openssl option. (Tatsuo Ishii)

Explicitly stat that it is -w option of pcp command is needed to use .pcppass. (Tatsuo Ishii)

Fix "2.8 Creating insert lock table" section. (Tatsuo Ishii)

It only referred to the native replication where it should have referred to the snapshot isolation mode.

Enhance SHOW POOL_CACHE. (Tatsuo Ishii)

Change pgpool_setup to append some parameters to the end of the configuration file. (Bo Peng)

Refactor 071.execute_and_deallocate/test.sh. (Tatsuo Ishii)

Enhance regression tests. (Tatsuo Ishii)

Fix occasional 005.jdbc test failure. (Tatsuo Ishii)