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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "" <etdirloth(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6019: invalid cached plan on inherited table
Date: 2011-05-11 04:17:24
Message-ID: 19015.1305087444@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"" <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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message 李紅兵 2011-05-11 05:53:56 Re: BUG #5994: Can't excute DBI->connect to oracle by client site
Previous Message Tom Lane 2011-05-11 03:46:05 Re: Changed behaviour of \'