[Tile-serving] [osm2pgsql-dev/osm2pgsql] Create prepared statements via libpq instead of SQL (Discussion #2118)

[Ryan Lambert]

Would it be possible to change how prepared statements are created in osm2pgsql? They currently are created using SQL statements. I'd like to suggest switching to use [`libpq PGPrepare`](https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQPREPARE) instead.

An example of a current command [from `properties.cpp`](https://github.com/osm2pgsql-dev/osm2pgsql/blob/bf00c512577ba4749c71aa4f28a889efbe339e11/src/properties.cpp#L94C1-L100) shows the SQL approach.

```cpp
    db_connection.exec(
        "PREPARE set_property(text, text) AS"
        " INSERT INTO {} (property, value) VALUES ($1, $2)"
        " ON CONFLICT (property) DO UPDATE SET value = EXCLUDED.value",
        table_name());
```


#### Use case

I'm suggesting this change because this appears to be the last blocking issue preventing using osm2pgsql with pgBouncer.  Now that [pgBouncer v1.21](https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0) is out with support for prepared statements, it should be possible to use.  Just not with statements prepared via SQL queries. From the [pgBouncer docs](https://www.pgbouncer.org/config.html#max_prepared_statements):

> "Note: This tracking and rewriting of prepared statement commands does not work for SQL-level prepared statement commands such as `PREPARE`, `EXECUTE`, `DEALLOCATE`, `DEALLOCATE ALL` and `DISCARD ALL`. "

#### Possible approach

It seems to me that functions could be added similar to `exec_prepared_internal` / `exec_prepared_with_result_format` to us `PGPrepare`.  Are there known reasons why this wouldn't work or would be a net negative impact?  I'd be willing to try writing a PR to make this change.  I'm not very experienced with C++ but think I can at least get a PoC out there if that'd be helpful. 


-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2118
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2118 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20231227/995c4e96/attachment.htm>