Re: Plan invalidation design - Mailing list pgsql-hackers

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Russell Smith <mr-russ@pws.com.au> writes:
>> Tom Lane wrote:
>>> 2. Given a handle for a previously stored query, check to see if the plan
>>> is still up to date; if not, regenerate it from the raw parse tree (note
>>> this could result in failure, eg if a column used by the query has been
>>> dropped).  Then return the plan tree.
>>> 
>> What do we do in the case of failure?  Die in the same way we do now 
>> when you can't use the plan that's been made?
>
> Well, the difference is that at plan use you might get an error that
> currently could only occur at initial query parsing.  I don't see that
> this is a big deal, but it will be a change in behavior.
>
> One thing I realized since yesterday is that it'll have to be possible
> for the caller to tell whether the plan has changed since he last saw
> it (perhaps via a re-plan counter included in the cache entry).  It's
> entirely possible that the set of output columns will have changed,
> and so the caller may need to re-do derived work.  For example plpgsql
> will need to re-do its analysis of whether a plan is "simple".

Hm. The set of output columns could change? How?

If you prepare "select *" and add a column, you're saying the query should
start failing? That seems strange given the behaviour of views, which is that
once parsed the list of columns is written in stone. It seems prepared queries
should work the same way that views work and remember which physical column
they were referring to previously. (Personally I don't like that behaviour but
it feels like this should be consistent with it.)

I guess you do have a serious problem if you redefine the type of a column or
redefine a view (though I think you would have to drop and recreate it, CREATE
OR REPLACE wouldn't let you change the output columns).

>>> We probably want to return a direct pointer to the cached plan tree
>>> instead of making a copy.  This should be safe, because the executor now
>>> treats plan trees as read-only, but it does mean that when plan
>>> invalidation occurs the cached plan tree might still be in use.
>
>> excuse my ignorance here, but under what circumstances is a plan in use 
>> for a single backend at the same time as it's invalidated.

Invalidation messages can occur at certain. If you access any new table or
object while the plan is still running, either because you're in a plpgsql
loop fetching records from it, or because some function you're calling in the
query runs some other sql against another table then you'll receive any
pending invalidation messages. 

It should only be possible to receive messages from operations that are legal
to execute while someone is using the object. So, for example, creating new
indexes. So if you're actively in the process of using the plan it shouldn't
be necessary to junk it.

Perhaps that means it would be handy to have two kinds of invalidation
messages. Hard invalidations mean that anybody with cached plans should
immediately junk them and throw up nasty errors and assertion failures if
they're in a state when that shouldn't happen. And Soft invalidations mean you
shouldn't start any new queries but any that are executing are still ok.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com