Re: Slowness of extended protocol - Mailing list pgsql-hackers
| From | Vladimir Sitnikov |
|---|---|
| Subject | Re: Slowness of extended protocol |
| Date | |
| Msg-id | CAB=Je-F2v=O4+_mzCJr4401xDo7Kk+iD+evhNKQfWGic+N-e4w@mail.gmail.com Whole thread Raw |
| In response to | Re: Slowness of extended protocol (Shay Rojansky <roji@roji.org>) |
| Responses |
Re: Slowness of extended protocol
|
| List | pgsql-hackers |
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
pgsql-hackers by date: