Re: Wishlist for 7.4: Plan stability

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Really it boils down to one point: there's really no reason to assume a user
> > should be able to execute any new query he feels like. Creating a new query
> > should be privileged operation just like creating a new table or new database.
>
> This is an interesting view of what a database should be like, but it
> has very little to do with my idea of a database ;-). I think you want
> some sort of middleware layer to keep your users away from the database.

This is how people attempt to tackle this problem with Oracle or other
databases, but it's an assbackwards design. It leads to a lot of pain and
awkwardness and only partially solves the problems. A good design would be
elegant and result in a much more manageable system.

What I'm really asking for is lower level control of when the query parser and
the optimizer runs. That would allow an admin or middleware to control when
new queries are parsed and optimized.

It could also allow an admin to peek at the existing queries and see what
plans are currently in the system, rather than run explain himself and say
"well this is what it would do if i ran it now, which might be the same thing
that ran earlier and caused this performance problem but there's no way to
know for sure"

> I do not agree that the DB itself ought to contain such draconian
> restrictions.

Note that the restriction I'm proposing be available is of the form "stop the
system from doing something for me". This is the kind of feature that's
impossible to graft on cleanly in a higher layer.

--
greg