get_wkb failed: ERROR: prepared statement "get_wkb" does not exist

[Roburetto]

Writing of an expire.list of tiles during database update is failing with the following:

Using 6 helper-processes
Left to process: 0...node cache: stored: 17824(100.00%), storage efficiency: 41.65% (dense blocks: 1, sparse nodes: 17301), hit rate: 19.47%
Osm2pgsql failed due to ERROR: get_wkb failed: ERROR: prepared statement "get_wkb" does not exist
(7)
Arguments were: 679300803,

Does anyone have a cue on why and how to fix it?

Thanks in advance.

For reference:
I am using the following command:

osm2pgsql -d databasename -H localhost -U username -W -S ~/OpenTopoMap/mapnik/osm2pgsql/opentopomap.style -P 5432 -s -C 800 -a ~/data/updates/changes.osc.gz -e15 -o expire.list

osm2pgsql version 0.96.0 (64 bit id space)
Compiled using the following library versions (Ubuntu 18.04):
Libosmium 2.15.1
Lua 5.2.4

[Roburetto]

Apparently the issue was a consequence of using a fairly old osm2pgsql version (0.92 - supplied by stock Debian 9.8). An update to 0.96 from stretch-backports fixed it.

[kropacf]

I have the same issue.
mod_tile runs osm2pgsql like this:

osm2pgsql -a --slim -e13-20 -d gis -o /var/lib/mod_tile/dirty_tiles.4661 /var/lib/mod_tile/changes.osc.gz

version:

osm2pgsql version 0.96.0 (64 bit id space)

Compiled using the following library versions:
Libosmium 2.15.1
Lua 5.2.4

Is there any idea how to fix it?

[Roburetto]

Now, that I look at my own issue it seems I mixed the reasoning a bit, since I was simultaneously using Ubuntu (self compiled osm2pgsql ) and Debian (packages).
As described, I solved the issue on Debian using backports.
Ubuntu was just for testing. So I never came up with a solution on that front, sorry.

[goncalo-oliveira]

I'm seeing this with version 1.9.2 with PostGIS 3.3.

osm2pgsql version 1.9.2
Build: Release
Compiled using the following library versions:
Libosmium 2.19.0
Proj [API 6] 9.3.0
Lua 5.1.4 (LuaJIT 2.1.0-beta3)

Arguments used: --create --slim -C 2048 -v --hstore-all

The logs

2023-09-11 14:30:26  osm2pgsql version 1.9.2
2023-09-11 14:30:26  [00] Database version: 15.4
2023-09-11 14:30:26  [00] PostGIS version: 3.3
2023-09-11 14:30:27  [00] Found properties table 'osm2pgsql_properties': false.
2023-09-11 14:30:27  [00] Storing properties to table '"public"."osm2pgsql_properties"'.
2023-09-11 14:30:27  [00]   Storing attributes='false'
2023-09-11 14:30:27  [00]   Storing db_format='1'
2023-09-11 14:30:27  [00]   Storing flat_node_file=''
2023-09-11 14:30:27  [00]   Storing output='pgsql'
2023-09-11 14:30:27  [00]   Storing prefix='planet_osm'
2023-09-11 14:30:27  [00]   Storing style='/opt/homebrew/Cellar/osm2pgsql/1.9.2/share/osm2pgsql/default.style'
2023-09-11 14:30:27  [00]   Storing updatable='true'
2023-09-11 14:30:28  [00]   Storing version='1.9.2'
2023-09-11 14:30:28  [00] Reading file: data.osm.pbf
2023-09-11 14:30:28  [00] Started pool with 4 threads.
2023-09-11 14:30:28  [00] Mid: pgsql, cache=2048, db_format=1
2023-09-11 14:30:28  [00] Setting up table 'planet_osm_nodes'
NOTICE:  table "planet_osm_nodes" does not exist, skipping
2023-09-11 14:30:28  [00] Setting up table 'planet_osm_ways'
NOTICE:  table "planet_osm_ways" does not exist, skipping
2023-09-11 14:30:28  [00] Setting up table 'planet_osm_rels'
NOTICE:  table "planet_osm_rels" does not exist, skipping
2023-09-11 14:30:29  [00] Using projection SRS 3857 (Spherical Mercator)
2023-09-11 14:30:29  [00] Using built-in tag transformations
2023-09-11 14:30:29  [00] Middle 'pgsql' options:
2023-09-11 14:30:29  [00]   nodes: true
2023-09-11 14:30:29  [00]   untagged_nodes: true
2023-09-11 14:30:29  [00]   db_format: 1
2023-09-11 14:30:29  [00]   use_flat_node_file: false
2023-09-11 14:30:29  [00]   way_node_index_id_shift: 5
2023-09-11 14:30:29  [00]   with_attributes: false
2023-09-11 14:30:30  [00] Setting up table 'planet_osm_point'
NOTICE:  table "planet_osm_point" does not exist, skipping
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
2023-09-11 14:30:31  [00] Setting up table 'planet_osm_line'
NOTICE:  table "planet_osm_line" does not exist, skipping
NOTICE:  table "planet_osm_line_tmp" does not exist, skipping
2023-09-11 14:30:31  [00] ERROR: Database error: ERROR:  prepared statement "get_wkb" already exists

Postgres is hosted with neon.

[goncalo-oliveira]

With --log-sql enabled, I can see the get_wkb prepare statement more than once...

[00] SQL: (C5) PREPARE get_wkb(int8) AS SELECT way FROM "public"."planet_osm_point" WHERE osm_id = $1
[00] SQL: (C7) PREPARE get_wkb(int8) AS SELECT way FROM "public"."planet_osm_line" WHERE osm_id = $1

These are coming from two distinct connections, apparently.

[goncalo-oliveira]

Ok, seems that this has something to do with connection pooling. If I don't use a connection pooler, I don't see this error.

[goncalo-oliveira]

Alright, answering my own issue here, as it's a non-issue. Well, not an osm2pgsql issue at least.

Neon uses PgBouncer in transaction mode, which does not support Postgres features such as prepared statements or LISTEN/NOTIFY. For a complete list of limitations, refer to the "SQL feature map for pooling modes" section in the pgbouncer.org Features documentation.

Bottom line: PREPARE statements are not supported.