Le 10 janv. 2015 20:04, "Levente Uzonyi" <le...@elte.hu> a écrit : > > The V2 version of the protocol doesn't support prepared statements. > > When we were writing the PostgresV3 package, we planned to implement prepared statements (via the extended query protocol), but later we decided to not do it. > > Why? > > The protocol is way more complex than the simple query protocol (which is used for regular queries), and involves more message sends, which can increase the network latency. > > The security that prepared statements give is equivalent to proper escaping. That can and should be implemented for standard queries too. > > The performance "improvement" what prepared statements give has two sides. You save the cost of parsing and planning, but the query is executed with the same plan, no matter what the arguments are. This can lead to sub-optimal performance. > > With the extended query protocol you can execute one statement per query. With the simple query protocol you can execute as many as you want. This can increase the network latency significantly for prepared statements. > > In PostgreSQL, calling functions using the simple query protocol behave similarly to prepared statements. You don't have to pass your SQL query to the server all the time, just the ame of the function and its arguments wrapped in a very simple SQL query. > This way the plan is created when the function is executed the first time (so it's even superior to prepared statements, because there's no per-connection parsing and planning cost). > > Functions also let you to use any procedural language PostgreSQL supports, which are currently SQL, PL/pgSQL, Pl/Tcl, PL/Perl, PL/Python. Using the high level languages makes it easier to write complex queries, or express complex logic, which wouldn't be possible with a single SQL query. > > So we decided to use functions instead. We added some image side tools to make it easier to write, save, load and debug functions. Currently only the PL/pgSQL language is supported by these tools, and there are some other limitations, but we'll implement new features as time permits. > > What about performance? > > Using our toolchain, a single process can execute 2500-3000 queries (using a modern CPU, and CogVM) per second, and an image can make about 5-6k queries per second overall. > This includes the overhead of using the connection pool, and the generation of the textual SQL query for each function call. Without these it's possible to go above 10k/second. >
About connection pool: how does one uses it? Is there a doc somewhere ? Phil > Text or binary? > > We implemented the text-based protocol, but added the hooks for the binary protocol too. Why? > The binary protocol is undocumented (its documentation is the C source code), and it's subject to change with each release. > The text-based protocol is a bit better documented, and it should work with all versions of PostgreSQL starting from 7.4. > > Levente > > > On Sat, 10 Jan 2015, David Carlos Manuelda wrote: > >> I've installed and tested PostgresV2 under pharo with the following doits >> >> Gofer new >> smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; >> configuration; >> load. >> (#ConfigurationOfPostgresV2 asClass project version: '2.4') load >> >> While it works good, it is missing a very important feature from both >> security and performance point of view: The prepared statements. >> >> As a brief, prepared statements are parameterized SQL statements that are >> loaded ONCE per connection instead of sending the whole query to DB every >> time, and also, they are parameterized, so it completelly prevents SQL >> injection, as the parameters are automatically 'detected' and scaped and/or >> handled accordingly without allowing in any case a parameter to alter the >> SQL meaning, which can happen by using regular SQL queries made by string >> concatenation. >> >> I browsed the class and did not find any prepare: method nor anything >> similar. >> >> Also, you can give a name to a SQL sentence, which makes the code much more >> readable without messing too much logic with SQL commands and string >> concatenation. >> >> An example is as follows: (supposing we have an instance variable >> connection, already initialized and connected via PGConnection class) >> >> Instead of: >> >> self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser >> userName, ''';'. >> >> Would be something like this: >> self connection executePrepared: 'getUserData' with: anUser userName. >> >> And another suggestion could be something like: >> self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable >> WHERE name=$1' >> >> Any plan for this to be implemented or any hint to other PostgreSQL class >> that already has it? >> >> David. >> >> P.S. I've written in my blog about this some time ago: >> http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.html if someone finds it useful. >> >> >> >
