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

[Anssi Kääriäinen]

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.

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.

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?

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.

  - Anssi Kaariainen