[Pgpool-hackers] Transforming SQL to prepared statements inside pgpool-II

[Tatsuo Ishii]

> Hello all,
> 
> I have this crazy idea of transforming SQL to prepared statements
> inside the pooler. The reason for this is simple: for my web
> application (using Django ORM), some queries take around 5ms to plan
> and <1ms to execute, so it would be nice to get rid of this overhead.
> 
> The plan is as follows:
> 
> 1. Users define manually (in some configuration file) which SQL should
> be transformed to prepared statements, for example:
> select * from test_table where id = $1;
> select * from test_table tt join test_table2 tt2 on tt.id = tt2.id and
> tt2.name like $1 and tt.id < $2;
> (possibility for future, transform the query to function execution:
> select * from test_table where id = $1; => select * from
> test_table_f($1);)
> 
> 2. The pooler parses the queries, and sees if they match any of the
> defined statements.
>   - if match, see if there is a prepared statement created for the query
>   - for the current connection
>     - if yes, execute the statement
>     - if no, prepare the query, store information that this query has been
>     - prepared for this connection and execute it.
>   - if no match, just run the query normally.
> 
> 3. That is pretty much it.

Interesting idea.

> Now, the hard part seems to be parsing the given query. For my
> purposes it is fine that the match needs to be exact. But I need to
> match the ? in the configuration SQL to the constants in the
> query. So, a query like "select * from test_table where id = 1" needs
> to be somehow transformed to something like "select * from test_table
> where id = $1", $1 = 1. Is there code existing in pgpool-II to parse
> the query in a way that would allow to do the transformation in
> somewhat sane amount of work? If not, it seems this is going to be too
> much work.

We have SQL parser stolen from PostgreSQL. It parses SQL and creates a
parse tree. Also we have a tree walker
function(raw_expression_tree_walker), which can be used to transform
particular expression to another(example: timestamp expression
rewiting). See pool_select_walker.c for more details.

> Am I missing something obvious why this would be harder to implement
> than it seems? Is there something similar already existing in
> pgpool-II or other poolers?

Well protocol handling of simple query(regular SQL) and prepared query
is totally different. Simple query protocol is simple: just send a
query and receive the result. The protocol used for prepared query
(called "extended query protocol") is much more complex: it has
several phases: parse, bind, execute. I suggest you carefully read
"Frontend/Backend Protocol" section of PostgreSQL document. However I
can think of anything which makes your idea impossible at this point.

> I have some experience in C and almost no experience hacking pgpool-II
> or PostgreSQL, so I do not expect to get anything more done than an
> interesting experiment.

I will gladly help you understanding the internal of pgpool-II.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp