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