Levente Uzonyi wrote:
> 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
>
Well, I want to point a thing to be considered regarding prepared
statements.
It is not only about security/performance, using them also helps to have
your code cleaner and read it better.
* No need to use ' character for text elements inside SQL query
Avoid the mess that: execute: 'INSERT INTO table VALUES (''', aUser
id, ''', ''', Auser otherField, ''');'
Confusing and hard to read '
Instead, with prepared is something like 'INSERT INTO table
VALUES ($1,$2)'
* No need to filter data ( even more readable code )
* Ability to name the SQL so you can use it later by name ( for comodity
)
Just take this in consideration if it is going to be evaluated for
implementation :)
> 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.
>>
>>
>>
