Named / preparsed / preplaned(prepared) queries - Feature proposal - Mailing list pgsql-general

Hi all

I have an idea how to extend postgresql feature set.


I sugest named / preparsed / preplamned query  mechanizm where named query is a simpliest form and preplaned query is
themost sofisticated (implementation wise). Ie to have ability to  assign a global and may be persistant NAME (or
alias)for some query for some user. Similar how PREPARE related stuff does, but AFAIK, prepared statement are limited
forthe current user on current connection only.  

with syntax like this:

NAMEDQUERY "select foo from bar where blah=$1" AS "myquery" [PREPARSED | PREPLANED  REPLAN EVERY condition]
[PERSISTENT][FINAL] [FOR USER user1,user2] 


And then  modify user privileges to add privileges to create a NAMEDQUERY, to create it for others -  typicaly  useful
fordatabase administrator and the  privilege of executing NAMED QUERY ONLY for some user - for example guest. 


PERSISTANT tell the server that this named query should survive server restart.

FINAL: if Administrator set a named query as FINAL, no other user is able to override it.

PREPARSED: Rationale behind PREPARSED queries is that in most scenarios on web applications there is a simple not
complicatedselect of data often in heap or memory or in the in memory index where actually fetching that data is faster
thenparsing a query string. So I thing a global cache of preparsed  NAMEDQUERY statements is a good idea and on some
loadscan boost performance significantly. 

PREPLANED: same as the preparsed, but the query plan is cached too. So in frequent simple queries (like common in
ajax/cometapplications where it ofen do some simple select to query a status of session etc), I thing it can remove
significantburden (parsing and planning a query) from CPU . 

yes, I know that a query plan that is superior  now, could be inferior few minutes later. So I suggest a  REPLAN EVERY
condition,where condition can be whatever user like - time interval,   changed N percent of tuples etc etc - Let user
decidewhat mechanizm is best for him.   


This feature can lead to two interested things. First, there is a decoupuling a database from application level and
keepthis two realm relatively separate, if underlying db structure is changed, just redefine named queries and do not
touchapplication. It create some sort of easy to use abstraction layer. It is a bit "objectish" approach, because you
cancreate a selfcontained database with data and method for its manipulation (if named query is  set as PERSISTENT,
should dump/restore and replication solutions do their job on persistant named queries as well)   


Second thing is a powerful, simple, yet easy to use security feature with as much granularity as is possible. For
exampleuser guest, who have set a NAMED QUERY ONLY user privilege, will be limited only to queries that admin of the
databasedefined for him, no matter what.I thing that it could be easy to use privilege sepration mechanizm.  

I use postgresql  in my projects since 6.x branch and I am happy with it, but proposed feature is on my Postgresql
wishlist. 
Any comments wellcome.

PS: Execuse my wrong english.