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

Michiel van Leening - ScreenCom michiel at screencom.eu
Tue Dec 20 01:11:55 JST 2022


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?

-- 
Kind regards,

Michiel van Leening
Senior Systems Architect
ScreenCom B.V.

There's a one-liner for everything. :)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20221219/20fe84f5/attachment.htm>


More information about the pgpool-general mailing list