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.
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.
