Re: BUG #6019: invalid cached plan on inherited table

"" <etdirloth(at)gmail(dot)com> writes:
> Cached execution plan of SQL stored procedure (which select from inherited
> table) executed from within PLPGSQL function is used even when inheritance
> descendant is already removed.

Don't hold your breath waiting for a fix for that :-(. There isn't any
support for detecting plan-invalidation events for SQL-language
functions, and in most situations it would be overkill because the plans
are only cached for the lifespan of one calling query anyway. The only
case where the plan could survive longer is this specific example of a
SQL function called from a "simple expression" in a plpgsql function,
and even then it only survives for one complete transaction.

I've thought for some time that SQL functions should be reimplemented
to cache information more the way plpgsql functions do, ie, with a
session-lifespan data structure for each function. If we did that
and made the individual query plans be stored in plancache, then
invalidation of this type would occur automatically --- and, not
insignificantly, the plans would survive long enough to pay back the
added overhead of tracking invalidation causes for them.

A somewhat related point is that people have occasionally complained
because all the queries in a SQL function are run through parse analysis
at once, causing things like

CREATE TABLE foo ... ;
INSERT INTO foo ... ;

to not work because the second command is analyzed before the first is
executed. If we were reimplementing SQL functions then it'd make sense
to try to do something about that at the same time.

None of this is on the TODO list, but I'll go fix that momentarily.

regards, tom lane