Create prepared statements via libpq instead of SQL

[rustprooflabs]

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 instead.

An example of a current command from properties.cpp shows the SQL approach.

    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 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:

"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.

[joto]

Looking at that function I forsee one possible problem: You have to define the parameter types using const Oid * which is more cumbersome then just writing out the SQL code. You have to find out what the Oid ist for all the types used. Apparently the type can be inferred automatically, but I am not sure this will work in all cases. Feel free to give it a try though.

[joto]

I read the docs more carefully and it is not necessary to figure out the Oids. I have started to implement this and some parts are already merged (f4ace45). But more changes are needed and they need some other changes first...

[joto]

This has been fixed in the mean time and was released in version 2.1.0.