Automatically prepare statements based on use

[roji]

Triggered by @vlsi's comment: #1227 (comment)

Basically, keep track of often-executed queries and implicitly prepare them once an execution threshold is reached. Manage these implicitly-prepared statements with an LRU cache to prevent explosion.

Think more about what exactly this means.

[roji]

FYI (unrelated) the idea of persisting prepared connections across close/open in pooled connections has been implemented in #483. It's a huge performance optimization and our conversation on the pgsql-hackers list is making me think that it should be on by default (it's currently opt-in).

However, Npgsql doesn't have any sort of mechanism for automatically preparing statements, similar to what you do in pgjdbc. The approach up to now has been to leave this up to the user - if the user chooses to, they can definitely prepare, but Npgsql won't do it implicitly for them. I can definitely see the merit of automatic preparation, the only objection I have right now is a general aversion of implicit/magical mechanisms like this - in general I prefer APIs to be very simple and do exactly what they mean, with a minimum of under-the-hood logic. But I'll definitely think about this.

[vlsi]

our conversation on the pgsql-hackers list is making me think that it should be on by default (it's currently opt-in)

good

But I'll definitely think about this.

Just in case, there are caveats:

1. If application is doing "alter table ... add column", a server-prepared statement that does "select * from ..." would fail with "not implemented" / "cached plan must not change result type"

2. If application does "deallocate all" (why?), all server-prepared statements will require re-preparation. Unfortunately, there are applications that do "deallocate all" for some unknown reason.

Here's relevant pgjdbc fix: https://github.com/pgjdbc/pgjdbc/pull/451/files