Re: Slowness of extended protocol - Mailing list pgsql-hackers
| From | Vladimir Sitnikov |
|---|---|
| Subject | Re: Slowness of extended protocol |
| Date | |
| Msg-id | CAB=Je-EFM1MBqByE2vSoCZeveUnJie=9pSP_Qz4tCbi6RE05BA@mail.gmail.com Whole thread Raw |
| In response to | Re: Slowness of extended protocol (Shay Rojansky <roji@roji.org>) |
| List | pgsql-hackers |
Shay>To be honest, the mere idea of having an SQL parser inside my driver makes me shiver.
Same for me.
However I cannot wait for PostgreSQL 18 that does not need client-side parsing.
Shay>We did, you just dismissed or ignored them
Please prove me wrong, but I did provide a justified answer to both
yours: https://www.postgresql.org/message-id/CAB%3DJe-FHSwrbJiTcTDeT4J3y_%2BWvN1d%2BS%2B26aesr85swocb7EA%40mail.gmail.com (starting with "Why the connection is reset")
and Robert's examples: https://www.postgresql.org/message-id/CAB%3DJe-GSAs_340dqdrJoTtP6KO6xxN067CtB6Y0ea5c8LRHC9Q%40mail.gmail.com
Shay>so your driver isn't faster than other drivers. It's faster only when used by lazy programmers.
I'm afraid you do not get the point.
ORMs like Hibernate, EclipseLink, etc send regular "insert ... values" via batch API.
For the developer the only way to make use of "multivalues" is to implement either "ORM fix" or "the driver fix" or "postgresql fix".
So the feature has very little to do with laziness of the programmers. Application developer just does not have full control of each SQL when working though ORM.
Do you suggest "stop using ORMs"? Do you suggest fixing all the ORMs so it uses optimal for each DB insert statement?
Do you suggest fixing postgresql?
Once again "multivalues rewrite at pgjdbc level" enables the feature transparently for all the users. If PostgreSQL 10/11 would improve bind/exec performance, we could even drop that rewrite at pgjdbc level and revert to the regular flow. That would again be transparent to the application.
Shay>are you sure there aren't "hidden" costs on the PostgreSQL side for generating so many implicit savepoints?
Technically speaking I use the same savepoint name through bind/exec message.
Shay>What you're doing is optimizing developer code, with the assumption that developers can't be trusted to code efficiently - they're going to write bad SQL and forget to prepare their statements
Please, be careful. "you are completely wrong here" he-he. Well, you list the wrong assumption. Why do you think my main assumption is "developers can't be trusted"?
The proper assumption is: I follow Java database API specification, and I optimize pgjdbc for the common use case (e.g. ORM or ORM-like).
For instance, if Java application wants to use bind values (e.g. to prevent security issues), then the only way is to go through java.sql.PreparedStatement.
Here's the documentation: https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#prepareStatement-java.lang.String-
Here's a quote:
Javadoc> Note: This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method
prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certainSQLException objects.The most important part is "if the driver supports precompilation..."
There's no API to enable/disable precompilation at all.
So, when using Java, there is no such thing as "statement.enableServerPrepare=true".
It is expected, that "driver" would "optimize" the handling somehow in the best possible way.
It is Java API specification that enables me (as a driver developer) to be flexible, and leverage database features so end user gets best experience.
Vladimir
pgsql-hackers by date: