Re: first time hacker ;) messing with prepared statements

> PFC wrote:
>>     Hello,
>>     So, I embarked (yesterday) on a weekend project to add a new  
>> feature to Postgres...
>>     I use PHP with persistent connections and always have been bothered  
>> that those very small AJAX queries (usually simple selects returning 1  
>> row) take more CPU in postgres to parse & plan than to actually execute.
> Microsoft's answer to this issue with SQLServer appears to have been to  
> introduce a smart
> cache for all statement plans. It seems to be very effective. I guess  
> you're doing much the
> same thing but with more user intervention,  in effect.
Actually, the main purpose was to 1) have fun hacking Postgres, and 2)  
perhaps something useful would come of it...And I did find it very interesting, probably due to the fact that  
Postgres source code is so... impressively clean... well organized...  
readable... it took two hours from downloading the source to having an  
extra functional system catalog, and it worked at the first compile, all  
due to the concise but to the point comments in include/catalog, I  
couldn't believe it.
Anyway, Microsoft's solution is cool, too, but you need to reparse the  
entire query to then detect "I've planned this query before, with other  
parameters, so I'll reuse that prepared plan", so it adds another parsing  
step, which is less efficient.Postgres could also do that with a small modification, by the way : like  
by using the entire string (with $1 style parameters) instead of the  
statement name, use that as a cache key, and send parameters separately,  
but I think it would be less clean than, say, a statement called  
"get_user_by_id" or something.Also I like the idea of named prepared queries, which feel a bit like  
procedures, because many of those small, often-used queries would end up  
being defined in the same place, which makes schema changes (and having to  
modify queries in your application) slightly less painful.PHP can also use pg_exec() which sends the parameters
separately, 
 
automagically converted to postgres format, so you save time and hassle on  
quoting and de-quoting versus a SQL EXECUTE.Since the performance of pg_exec() is almost twice as fast as plain SQL,  
and PHP scripts tend to use quite a lot of these queries, it also means a  
free database server performance upgrade (ie. the same DB server can  
handle more webservers, for instance).
Another possible implementation would be to use a connection pooler  
which, when opening a new connection, can be configured to send a SQL  
script containing all the PREPARE statements.

> Are you sure that you application wouldn't benefit more from a MOM  
> solution with
> persisted database connections? Have you looked at  
> http://safmq.sourceforge.net/?
Dunno. Is this related to Postgres ?