<html theme="default-dark" iconset="color"><head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body
text="#000000">
Hi all,<br>
<br>
I've got a situation where the query I send to pgPool is being
transformed into an invalid query in the backend.<br>
<br>
Environment:<br>
<br>
docker, running <br>
<a class="moz-txt-link-freetext" href="postgres:12.12-alpine">postgres:12.12-alpine</a> primary + replica (also happens on
15.1-alpine)<br>
bitnami/pgpool:4.4.0 (did not try lower version due to
snapshot_isolation requirement)<br>
<br>
backend_clustering_mode = 'snapshot_isolation'<br>
<br>
The table that's giving the problems is setup as follows:<br>
<br>
<span style="font-family: monospace;">CREATE TABLE SYSTEM_INFO</span><span
style="font-family: monospace;"><br>
</span><span style="font-family: monospace;">(</span><span
style="font-family: monospace;"><br>
</span><span style="font-family: monospace;"> INFO_TYPE VARCHAR(20)
NOT NULL,</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;"> AUTH_ENABLE BOOLEAN
DEFAULT FALSE,</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;"> MODIFY_DATE TIMESTAMP
DEFAULT now(),</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;"> USE_PERIOD BOOLEAN
DEFAULT TRUE,</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;"> PERIOD DECIMAL(10, 0)</span><span
style="font-family: monospace;"></span><span style="font-family:
monospace;"><br>
</span><span style="font-family: monospace;">);</span><br>
<br>
The query that's being sent to pgPool is the following<br>
<br>
<span style="font-family: monospace;"> INSERT INTO SYSTEM_INFO (</span><span
style="font-family: monospace;"><span style="font-family: monospace;">INFO_TYPE</span>,
USE_PERIOD) VALUES ('PASSWORD', false);</span><span style="font-family:
monospace;"><br>
</span><br>
The error I get in the psql console is the following<br>
<br>
<span style="font-family: monospace;"> ERROR: syntax error at or
near ","</span><br>
<br>
The errors that is being logged in docker are<br>
<br>
<span style="font-family: monospace;">primary_1 | 2022-12-19
15:39:14.735 GMT [341] ERROR: syntax error at or near "," at character
94</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;">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')</span><span
style="font-family: monospace;"><br>
</span><span style="font-family: monospace;">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 ",""</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;">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 ",""</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;">replica-1_1 | 2022-12-19
15:39:14.735 GMT [340] ERROR: syntax error at or near "," at character
94</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;">replica-1_1 | 2022-12-19
15:39:14.735 GMT [340] STATEMENT: INSERT INTO "</span><span
style="font-family: monospace;"><span style="font-family: monospace;">system_info</span>"("info_type",
"use_period", "modify_date") VALUES ('PASSWORD',,'2022-12-19
15:39:14.729872+00')</span><span style="font-family: monospace;"><br>
</span><span style="font-family: monospace;"><br>
</span>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.<br>
<br>
The query is not created by me, but by an application. It executes fine
(in its original form) when I access primary_1 directly.<br>
<br>
The following query also fails<br>
<br>
<span style="font-family: monospace;"> INSERT INTO SYSTEM_INFO
(INFO_TYPE, USE_PERIOD, PERIOD) VALUES ('ALTERNATE', true, 30);</span><br>
<br>
which is being transformed into<br>
<br>
<span style="font-family: monospace;"> INSERT INTO
"system_info"("info_type", "use_period", "period", "modify_date") VALUES
('STORED_DEVICE',,30,'</span><span style="font-family: monospace;"><span
style="font-family: monospace;">2022-12-19 15:40</span>:00:19.311693+00')</span><br>
<br>
I hope someone can shed light on this. Perhaps it's a bug?<br>
<br>
<div class="moz-signature">-- <br><span style="font-family:
Helvetica,Arial,sans-serif; font-size: 10pt;"></span>Kind regards,<br>
<p style="font-family: Helvetica,Arial,sans-serif; font-size: 10pt;">Michiel
van Leening<br>
Senior Systems Architect<br>
ScreenCom B.V.<br>
<br>
<span style="font-size: 9pt; color: rgb(208, 215, 225);">There's a
one-liner for everything. :)</span><br>
</p>
</div>
</body>
</html>