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
_______________________________________________
Pgpool-hackers mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-hackers
