[pgpool-general: 8872] Re: Prepared statements over pgpool ?
Charly Koza
cka at f4-group.com
Wed Jul 5 21:47:01 JST 2023
Here are the logs (limited to id 29, to avoid cluttering, however all
the 29 logs are here)
Jul 5 12:19:38 bigjaja pgpool[29]: [81-1] 2023-07-05 12:19:38.921:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [81-2] 2023-07-05 12:19:38.921:
osm2pgsql pid 29: DETAIL: query: "SELECT name, setting FROM pg_settings"
Jul 5 12:19:38 bigjaja pgpool[29]: [82-1] 2023-07-05 12:19:38.923:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [82-2] 2023-07-05 12:19:38.923:
osm2pgsql pid 29: DETAIL: query: "SELECT
regexp_split_to_table(extversion, '\.') FROM pg_extension WHERE
extname='postgis'"
Jul 5 12:19:38 bigjaja pgpool[29]: [83-1] 2023-07-05 12:19:38.925:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [83-2] 2023-07-05 12:19:38.925:
osm2pgsql pid 29: DETAIL: query: "SELECT count(*) FROM pg_tables WHERE
schemaname='public' AND tablename='planet_osm_nodes'"
Jul 5 12:19:38 bigjaja pgpool[29]: [84-1] 2023-07-05 12:19:38.926:
osm2pgsql pid 29: LOG: Terminate message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [85-1] 2023-07-05 12:19:38.927:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [85-2] 2023-07-05 12:19:38.927:
osm2pgsql pid 29: DETAIL: query: "SELECT relname FROM pg_class WHERE
relkind='i' AND relname = 'planet_osm_ways_nodes_bucket_idx';"
Jul 5 12:19:38 bigjaja pgpool[29]: [86-1] 2023-07-05 12:19:38.927:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [86-2] 2023-07-05 12:19:38.927:
osm2pgsql pid 29: DETAIL: query: "UPDATE pg_settings SET setting = '-1'
WHERE name = 'jit_above_cost'"
Jul 5 12:19:38 bigjaja pgpool[29]: [87-1] 2023-07-05 12:19:38.928:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [87-2] 2023-07-05 12:19:38.928:
osm2pgsql pid 29: DETAIL: query: "UPDATE pg_settings SET setting = '0'
WHERE name = 'max_parallel_workers_per_gather'"
Jul 5 12:19:38 bigjaja pgpool[29]: [88-1] 2023-07-05 12:19:38.929:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [88-2] 2023-07-05 12:19:38.929:
osm2pgsql pid 29: DETAIL: query: "PREPARE mark_ways_by_node(int8) AS
SELECT id FROM "planet_osm_ways" WHERE nodes && ARRAY[$1];
Jul 5 12:19:38 bigjaja pgpool[29]: [88-3] #011"
Jul 5 12:19:38 bigjaja pgpool[29]: [89-1] 2023-07-05 12:19:38.929:
osm2pgsql pid 29: LOG: Query message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [89-2] 2023-07-05 12:19:38.929:
osm2pgsql pid 29: DETAIL: query: "PREPARE mark_rels_by_node(int8) AS
SELECT id FROM "planet_osm_rels" WHERE parts && ARRAY[$1] AND
parts[1:way_off] && ARRAY[$1];
Jul 5 12:19:38 bigjaja pgpool[29]: [89-3] #011PREPARE
mark_rels_by_way(int8) AS SELECT id FROM "planet_osm_rels" WHERE parts
&& ARRAY[$1] AND parts[way_off+1:rel_off] && ARRAY[$1];
Jul 5 12:19:38 bigjaja pgpool[29]: [89-4] #011"
(removed some cluttering on other ids)
Jul 5 12:19:38 bigjaja pgpool[29]: [90-1] 2023-07-05 12:19:38.963:
osm2pgsql pid 29: LOG: Bind message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [90-2] 2023-07-05 12:19:38.963:
osm2pgsql pid 29: DETAIL: portal: "", statement: "mark_ways_by_node"
Jul 5 12:19:38 bigjaja pgpool[29]: [91-1] 2023-07-05 12:19:38.965:
osm2pgsql pid 29: LOG: Describe message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [91-2] 2023-07-05 12:19:38.965:
osm2pgsql pid 29: DETAIL: portal: ""
Jul 5 12:19:38 bigjaja pgpool[29]: [92-1] 2023-07-05 12:19:38.966:
osm2pgsql pid 29: LOG: Execute message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [92-2] 2023-07-05 12:19:38.966:
osm2pgsql pid 29: DETAIL: portal: ""
Jul 5 12:19:38 bigjaja pgpool[29]: [93-1] 2023-07-05 12:19:38.966:
osm2pgsql pid 29: LOG: Sync message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [94-1] 2023-07-05 12:19:38.966:
osm2pgsql pid 29: LOG: Bind message from frontend.
Jul 5 12:19:38 bigjaja pgpool[29]: [94-2] 2023-07-05 12:19:38.966:
osm2pgsql pid 29: DETAIL: portal: "", statement: "mark_rels_by_way"
Jul 5 12:19:38 bigjaja pgpool[29]: [95-1] 2023-07-05 12:19:38.966:
osm2pgsql pid 29: FATAL: unable to bind
Jul 5 12:19:38 bigjaja pgpool[29]: [95-2] 2023-07-05 12:19:38.966:
osm2pgsql pid 29: DETAIL: cannot get parse message "mark_rels_by_way"
Target postgres is "PostgreSQL 14.6 (Debian 14.6-1.pgdg110+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110,
64-bit"
with PostGIS : "POSTGIS=""3.3.2 4975da8"" [EXTENSION] PGSQL=""140""
GEOS=""3.11.1-CAPI-1.17.1"" SFCGAL=""1.3.8"" PROJ=""7.2.1"" GDAL=""GDAL
3.2.2, released 2021/03/05"" LIBXML=""2.9.10"" LIBJSON=""0.15""
LIBPROTOBUF=""1.3.3"" WAGYU=""0.5.0 (Internal)"" TOPOLOGY RASTER"
Do you want me to file a bug in the tracker ?
Le 05/07/2023 à 13:55, Tatsuo Ishii a écrit :
>> Hello !
>>
>> I'm trying to use pgpool (4.4.3) to pool connections to postgresql, to
>> enforce a lower max_connections.
>>
>> I'm getting errors on prepared statements from osm2pgsql, |Query is in
>> the form `|||EXECUTE mark_rels_by_way(404720865)|`
>> |
>>
>> |Errors logged by pgpool :
>> |
>>
>> FATAL: unable to bind
>>
>> DETAIL: cannot get parse message "mark_rels_by_way"
>>
>>
>> This looks a lot like an old unsolved issue from osm2pgsql
>> https://github.com/openstreetmap/osm2pgsql/issues/878
>>
>>
>> My current configuration include a single backend and
>>
>> backend_clustering_mode = 'raw'
>>
>> process_management_mode = dynamic
>>
>> process_management_strategy = lazy
>>
>> num_init_children = 150
>>
>> min_spare_children = 5
>>
>> max_spare_children = 20
>>
>> connection_cache = on
>>
>>
>> Are prepared statements possible over pgpool ? (looks like they should
>> be since documentation mention PREPARE/EXECUTE/DEALLOCATE commands)
> Yes, pgpool supports PREPARE/EXECUTE/DEALLOCATE.
>
>> Is there a specific configuration that breaks them ? or a specific
>> configuration to allow them ?
> No, they can be used without any specific configuration.
>
> Can you share pgpool log with:
>
> log_client_messages = on
>
> This will reveal what osm2pgsql is sending to pgpool and will be
> useful for investing the issue. Please make sure that the log file
> includes all logs since "mark_rels_by_way" first appears so that I can
> track how the messages are handled by pgpool.
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
More information about the pgpool-general
mailing list