No big deal to me, I just couldn't verify that it may occur only within
single transaction - not even in documentation.
thanks for the answer
.et.
On Wed, May 11, 2011 at 6:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "" <etdirloth@gmail.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
>