We need plan invalidation fix in 8.3 also at least it would make migrating from 8.2 to 8.3 much more attractive. Currenlty we are having problems related to plan invalidation couple of times per week (mainly we have to let developers change their code before we release it into live databases but it feels like sitting on ticking bomb after previous downtime). Is it possible to get it into some official 8.3.x release or should we do it in house?
Tom Lane wrote: > Martin Pihlak <martin.pihlak@gmail.com> writes: >> Changing statement result type is also currently prohibited in >> StorePreparedStatement. There maybe good reasons for this, > > How about "the SQL spec says so"? > > Admittedly, it's a bit of a jump from views to prepared statements, > but the spec is perfectly clear that altering a table doesn't alter > any views dependent on it: SQL99 11.11 <add column definition> saith
As you said it is a bit of a jump ... For one thing view definitions are persistent whereas statements are bound to be replanned sooner or later - reconnects etc. Disallowing replanning after invalidation just postpones it and meanwhile the cached plans are left unusable ("cached plan must not change result"). IMHO the problem should be left for the application to handle. Because this is where it will end up anyway.
Attached is a patch that implements plan invalidation on function DROP, REPLACE and ALTER. Function oids used by the query are collected in analyze phase and stored in PlannedStmt. Only plans that reference the altered function are invalidated. The patch also enables replanning on result set change.
