[pgpool-general: 8522] Re: Query is being sent to backend incorrectly

[Tatsuo Ishii]

Hi,

> Hi all,
> 
> I've got a situation where the query I send to pgPool is being
> transformed into an invalid query in the backend.
> 
> Environment:
> 
> docker, running
>     postgres:12.12-alpine primary + replica (also happens on 15.1-alpine)
>     bitnami/pgpool:4.4.0 (did not try lower version due to
> snapshot_isolation requirement)
> 
> backend_clustering_mode = 'snapshot_isolation'
> 
> The table that's giving the problems is setup as follows:
> 
> CREATE TABLE SYSTEM_INFO
> (
>     INFO_TYPE VARCHAR(20) NOT NULL,
>     AUTH_ENABLE BOOLEAN DEFAULT FALSE,
>     MODIFY_DATE TIMESTAMP DEFAULT now(),
>     USE_PERIOD BOOLEAN DEFAULT TRUE,
>     PERIOD DECIMAL(10, 0)
> );
> 
> The query that's being sent to pgPool is the following
> 
>     INSERT INTO SYSTEM_INFO (INFO_TYPE, USE_PERIOD) VALUES ('PASSWORD',
> false);
> 
> The error I get in the psql console is the following
> 
>     ERROR:  syntax error at or near ","
> 
> The errors that is being logged in docker are
> 
> primary_1    | 2022-12-19 15:39:14.735 GMT [341] ERROR:  syntax error at
> or near "," at character 94
> primary_1    | 2022-12-19 15:39:14.735 GMT [341] STATEMENT:  INSERT INTO
> "system_info"("info_type", "use_period", "modify_date") VALUES
> ('PASSWORD',,'2022-12-19 15:39:14.729872+00')
> pgpool_1     | 2022-12-19 15:39:14.735: psql pid 157: LOG: 
> pool_send_and_wait: Error or notice message from backend: : DB node id:
> 0 backend pid: 341 statement: "INSERT INTO "system_info"("info_type",
> "use_period", "modify_date") VALUES ('PASSWORD',,'2022-12-19
> 15:39:14.729872+00')" message: "syntax error at or near ",""
> pgpool_1     | 2022-12-19 15:39:14.735: psql pid 157: LOG: 
> pool_send_and_wait: Error or notice message from backend: : DB node id:
> 1 backend pid: 340 statement: "INSERT INTO "system_info"("info_type",
> "use_period", "modify_date") VALUES ('PASSWORD',,'2022-12-19
> 15:39:14.729872+00')" message: "syntax error at or near ",""
> replica-1_1  | 2022-12-19 15:39:14.735 GMT [340] ERROR:  syntax error at
> or near "," at character 94
> replica-1_1  | 2022-12-19 15:39:14.735 GMT [340] STATEMENT:  INSERT INTO
> "system_info"("info_type", "use_period", "modify_date") VALUES
> ('PASSWORD',,'2022-12-19 15:39:14.729872+00')
> 
> At some point, apparently, the boolean value for USE_PERIOD is removed.
> If I rewrite the query to use '1' for true of '0' for false, the query
> executes perfectly.
> 
> The query is not created by me, but by an application. It executes fine
> (in its original form) when I access primary_1 directly.
> 
> The following query also fails
> 
>     INSERT INTO SYSTEM_INFO (INFO_TYPE, USE_PERIOD, PERIOD) VALUES
> ('ALTERNATE', true, 30);
> 
> which is being transformed into
> 
>     INSERT INTO "system_info"("info_type", "use_period", "period",
> "modify_date") VALUES ('STORED_DEVICE',,30,'2022-12-19
> 15:40:00:19.311693+00')
> 
> I hope someone can shed light on this. Perhaps it's a bug?

Thank you for the report. Yes, it's a bug with Pgpool-II 4.4.

4.4 imported PostgreSQL 15's parser. A module in the parser to handle
boolean data type was not properly changed to adapt to pgpool and
emits empty string. I pushed a fix for this.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=f6cbfe25b14e9f5fde554c6fe2d42bf9adf60650

Next minor version, which is supposed to be out by the end of this
month, will include the fix.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp