<p></p>
<p dir="auto">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 <a href="https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQPREPARE" rel="nofollow"><code class="notranslate">libpq PGPrepare</code></a> instead.</p>
<p dir="auto">An example of a current command <a href="https://github.com/osm2pgsql-dev/osm2pgsql/blob/bf00c512577ba4749c71aa4f28a889efbe339e11/src/properties.cpp#L94C1-L100">from <code class="notranslate">properties.cpp</code></a> shows the SQL approach.</p>
<div class="highlight highlight-source-c++" dir="auto"><pre class="notranslate">    db_connection.exec(
        <span class="pl-s"><span class="pl-pds">"</span>PREPARE set_property(text, text) AS<span class="pl-pds">"</span></span>
        <span class="pl-s"><span class="pl-pds">"</span> INSERT INTO {} (property, value) VALUES ($1, $2)<span class="pl-pds">"</span></span>
        <span class="pl-s"><span class="pl-pds">"</span> ON CONFLICT (property) DO UPDATE SET value = EXCLUDED.value<span class="pl-pds">"</span></span>,
        <span class="pl-en">table_name</span>());</pre></div>
<h4 dir="auto">Use case</h4>
<p dir="auto">I'm suggesting this change because this appears to be the last blocking issue preventing using osm2pgsql with pgBouncer.  Now that <a href="https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0">pgBouncer v1.21</a> is out with support for prepared statements, it should be possible to use.  Just not with statements prepared via SQL queries. From the <a href="https://www.pgbouncer.org/config.html#max_prepared_statements" rel="nofollow">pgBouncer docs</a>:</p>
<blockquote>
<p dir="auto">"Note: This tracking and rewriting of prepared statement commands does not work for SQL-level prepared statement commands such as <code class="notranslate">PREPARE</code>, <code class="notranslate">EXECUTE</code>, <code class="notranslate">DEALLOCATE</code>, <code class="notranslate">DEALLOCATE ALL</code> and <code class="notranslate">DISCARD ALL</code>. "</p>
</blockquote>
<h4 dir="auto">Possible approach</h4>
<p dir="auto">It seems to me that functions could be added similar to <code class="notranslate">exec_prepared_internal</code> / <code class="notranslate">exec_prepared_with_result_format</code> to us <code class="notranslate">PGPrepare</code>.  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.</p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />Reply to this email directly, <a href="https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2118">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AA6353T6UHPH45QGUVANTWTYLSVDHAVCNFSM6AAAAABBE3GIYGVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZWGAYDKNBXGM">unsubscribe</a>.<br />You are receiving this because you are subscribed to this thread.<img src="https://github.com/notifications/beacon/AA6353VZEQGDGC4SICSNTMTYLSVDHA5CNFSM6AAAAABBE3GIYGWGG33NNVSW45C7OR4XAZNKIRUXGY3VONZWS33OVJRW63LNMVXHIX3JMTHAAW5C4E.gif" height="1" width="1" alt="" /><span style="color: transparent; font-size: 0; display: none; visibility: hidden; overflow: hidden; opacity: 0; width: 0; height: 0; max-width: 0; max-height: 0; mso-hide: all">Message ID: <span><osm2pgsql-dev/osm2pgsql/repo-discussions/2118</span><span>@</span><span>github</span><span>.</span><span>com></span></span></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2118",
"url": "https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2118",
"name": "View Discussion"
},
"description": "View this Discussion on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>