Re: TopPlan, again

On Sun, 2007-02-18 at 18:19 -0500, Tom Lane wrote:
> While thinking about having a centralized plan cache for managing plan
> invalidation, I got annoyed again about the fact that the executor needs
> access to the Query tree. This means that we'll be storing *three*
> representations of any cached query: raw parsetree for possible
> regeneration, plus parsed Query tree and Plan tree.
...
> After looking over the code it seems that the executor needs a limited
> subset of the Query fields, namely
...
> into
> intoOptions
> intoOnCommit (why is this separate from intoOptions?)
> intoTableSpaceName
...

> which I think we should put into a new TopPlan node type.

All else sounds good, but why would we be caching a plan that used these
fields? Anybody re-executing a CREATE TABLE AS SELECT on the same table
isn't somebody we should be helping. ISTM that we'd be able to exclude
them from the TopPlan on that basis, possibly creating an Into node to
reduce the clutter.

Couple of incidental points on plan invalidation:
- We need to consider how the planner uses parameter values. Currently
the unnamed query utilises the first bind parameters to plan the query.
Doing that when we have a central plan cache will definitely cause
problems in some applications which currently repeatedly re-specify the
same parameter on their session only, but differ across sessions. Sounds
bizarre, but assuming that all users of the same query want it optimised
the same way is not a good assumption in all cases. I'm completely in
favour of a centralized plan cache in all other ways...

- I'd like to make it impossible to re-plan the output columns of
queries with unspecified output columns e.g. * or foo.*
This makes it possible for the results of the query to change during
re-execution. I've never seen an application that used dynamic query
that allowed for the possibility that the result metadata might change
as we re-execute and allowing it would seem likely to break more
applications than we'd really want. It will also allow us to remove the
Metadata call from the v3 Protocol at Exec time, as David Strong
suggested last year on pgsql-jdbc.

- It would be good to allow for exec-time constraint exclusion, which
would allow caching plans that used by CE and stable functions (e.g. col
< CURRENT_DATE). That may change the design, even though thats not an
8.3 thing at all.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com