Re: Patch: plan invalidation vs stored procedures

Tom Lane wrote:
> Martin Pihlak <martin(dot)pihlak(at)gmail(dot)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