Re: Slowness of extended protocol

Shay> it's important to note that query parsing and rewriting isn't an
"inevitable evil".

Ok, I stay corrected. ADO.NET have raw mode in the API. That's interesting.

Let's say "lots of heavily used languages do have their own notion of bind
placeholders".
And for the reset, it is still not that hard to prepare on the go.

Shay> As Tom said, if an application can benefit from preparing, the
developer has the responsibility (and also the best knowledge) to manage
preparation, not the driver. Magical behavior under the hood causes
surprises, hard-to-diagnose bugs etc.

Why do you do C# then?
Aren't you supposed to love machine codes as the least magical thing?
Even x86 does not describe "the exact way the code should be executed".
All the CPUs shuffle the instructions to make it faster.

Shay>As Tom said, if an application can benefit from preparing, the
developer has the responsibility

Does developer have the responsibility to choose between "index scan" and
"table seq scan"? So your "developer has the responsibility" is like
building on sand.

Even "SQL execution on PostgreSQL is a magical behavior under the hood".
Does that mean we should drop the optimizer and implement "fully static
hint-based execution mode"? I do not buy that.

My experience shows, that people are very bad at predicting where the
performance problem would be.
For 80% (or even 99%) of the cases, they just do not care thinking if a
particular statement should be server-prepared or not.
They have absolutely no idea how much resources it would take and so on.

ORMs have no that notion of "this query must be server-prepared, while that
one must not be".
And so on.

It is somewhat insane to assume people would use naked SQL. Of course they
would use ORMs and alike, so they just would not be able to pass that
additional parameter telling if a particular query out of thousands should
be server-prepared or not.

Vladimir> "cached plan cannot change result type" -- PostgreSQL just fails
to execute the server-prepared statement if a table was altered.

Shay>How exactly do users cope with this in pgjdbc? Do they have some
special API to flush (i.e. deallocate) prepared statements which they're
supposed to use after a DDL?

First of all, pgjdbc does report those problems to hackers.
Unfortunately, it is still "not implemented".
Then, a workaround at pgjdbc side is made.
Here's relevant pgjdbc fix: https://github.com/pgjdbc/pgjdbc/pull/451

It analyzes error code, and if it finds "not_implemented
from RevalidateCachedQuery", then it realizes it should re-prepare.
Unfortunately, there is no dedicated error code, but at least there's a
routine name.

On top of that, each pgjdbc commit is tested against HEAD PostgreSQL
revision, so if the routine will get renamed for some reason, we'll know
that right away.

There will be some more parsing to cover "deallocate all" case.

Shay>it have been listed many times - pgbouncer

Let's stop discussing pgbouncer issue here?
It has absolutely nothing to do with pgsql-hackers.
Thanks.

Vladimir