Wishlist for 7.4: Plan stability

Ok, someone else posted their data warehousing wishlist, I want to post my
single item OLP wishlist: Plan stability. This is the natural conclusion of
having prepared queries.

This is one area where it would be possible to totally beat Oracle's
implementation by a huge margin. And it's something that's critical to web
sites and other applications that handle many short queries and need to
reliably provide rapid response.

What I want to do would actually go way beyond what Oracle provides. I have a
clear Idea what I would want to make the database truly manageable under heavy
OLP load.

I would want a queries to be a first class object in the postgres world. There
could be a system table that lists all the prepared queries the database knows
about. Every query's current plan and performance statistics could be linked
from there. This would allow a sysadmin to at least have a clue what queries
are running and how without having to open up the application source.

Then I would want an acl system to restrict under what circumstances postgres
allows new queries to be added, new plans to be analyzed, and old queries to
be removed.

While in a development server or a DSS server you want any user to be able to
create new queries and you want the database to optimize every query using the
most up to date information, in an OLP server that's not what you want. It's
more important to be consistently fast than it is to be as fast as possible.

Ideally I would expect every query to be manually loaded when a new version of
the application is loaded. A DBA could check at that point every plan and
ensure that they're all reasonable. Then the user that the web server connects
as would be prohibited from running any new queries or generating any new
plans for existing queries.

The DBA could go to sleep at night confident that the database isn't suddenly
going to hit some formerly unreached section of code or decide to "optimize" a
query differently and suddenly come to a crashing halt.

It would also mean that the query written by the new junior programmer you
just hired can't slip by QA and thrash the query cache of your database by
inserting constants directly into the query.

And finally, it would also mean that the query written by your other junior
programmer that inserts unchecked data can't become a gaping security hazard
because when the hacker submits subqueries in the form the resulting query is
rejected.

--
greg