Re: Plan invalidation vs. unnamed prepared statements

Tom Lane wrote:
> Gregory Stark  writes:
>   
>> Can we forcibly discard it if *any* messages are received that might
>> invalidate a plan? So basically it would work fine unless anyone in the system
>> does any DDL at all? I guess that has the downside of introducing random
>> unpredictable failures.
>>     
>
> Ugh :-(
>
>   
>> Or stash the query string and replan it (possibly in the query cache this
>> time) if someone executes it a second time?
>>     
>
> I think that's either my plan A or C.
>
> The main problem with uncontrolled replanning is that there's no way to
> detect a change in the query properties.  For example suppose the query
> is "SELECT * FROM foo" and we've already told the client (via Describe
> Statement) that that returns two integer columns.  If an inval now
> arrives because of "ALTER TABLE foo ADD COLUMN" (or perhaps worse, ALTER
> COLUMN TYPE), we've got a problem.  If we just blindly replan then we'll
> return tuples that do not match the previously given row description,
> which will certainly break most clients.
>      It will always be a good question what user expects as a result of 
'SELECT * FROM...'. For example, client may use ODBC or some other 
interface for DB communication. One the first step he retrieves 
information about the table and it's datatypes, on the second tries to 
fetch rows (using interface functions). Client application won't even 
guess that table could be changed between these two steps. It's 
impossible to avoid such situations, because we can't know how the user 
retrieves information about results he will expect.
> The plan caching module has enough infrastructure to detect and complain
> about these sorts of situations, and it also knows how to manage lock
> acquisition so that once we've decided a plan is still good, the tables
> won't change underneath us while we use the plan.  I don't see any way
> to make comparable guarantees without the overhead that goes with the
> cache manager.
>   
It's a required overhead. Result should be valid on the execution time, 
not on prepare. Cache manager is the best for this.
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>