Hi.
I'm a core developer on npgsql (the Postgresql .NET provider), we're hard at work on a major 3.0 version. I have a fundamental question that's been bugging me for a while and would like to get your opinion on it.
Npgsql currently supports three basic query modes: simple, client-side parameters and prepared. The first two use the Postgresql simple query flow (client-side parameters means the user specifies parameters programmatically, just like with prepared queries, but the actual substitution work is done client-side). Prepared uses the Postgresql extended query flow.
According to the Postgresql docs (49.2.2), the simple query flow, "the format of the retrieved values is always text". This creates a burden where npgsql needs to parse textual (and locale-specific!) info (e.g. dates, money). The situation is even worse when doing client-side parameters, since npgsql has to *create* textual representations that match what Postgresql is expecting. The workaround for this issue up to now has been to switch to culture-invariant formatting (e.g. lc_monetary=C), but this approach imposes the setting on users and affects functions in ways they don't necessarily want.
I would, in theory, love to switch the entire thing to binary and thereby avoid all textual parsing once and for all. If I understand correctly, this means all queries must be implemented as extended queries, with numerous extra client-server roundtrips - which are a bit hard to stomach. Section 49.1.2 of the manual also states that the unnamed prepared statement and portal are optimized for the case of executing a query only once, hinting that this is the proper way to do things - but this optimization still cannot not eliminate the extra roundtrips mentioned above (PREPARE, BIND, EXECUTE).
Can someone please let me know what the recommended/best practice here would be?
Thanks,
Shay