Re: [HACKERS] Patch: plan invalidation vs stored procedures

Hi

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?

Who should add it into september commitfest?

Asko


On Fri, Aug 15, 2008 at 2:13 PM, Martin Pihlak <martin.pihlak@gmail.com> wrote:

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.

regards,
Martin



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers