> On Fri, Oct 12, 2001 at 02:12:16PM -0400, Tom Lane wrote:
> >
> > The reason there's an issue is that plpgsql caches a query plan for the
> > "SELECT x FROM foo_1" query, and that plan is no good after you drop the
> > original version of foo_1; but we don't currently have a mechanism to
> > invalidate the cached plan.
>
> Why does putting BEGIN-END blocks around the queries, and thereby
> separating the transactions, not help? Should this not get around
> caching as each transaction is committed before proceeding to the next?
Currently, I believe the queries are cached for the life of the backend.
> > Workarounds: (1) use EXECUTE to avoid caching a plan for the problem
> > queries;
>
> Will using EXECUTE only for the "CREATE TABLE ... SELECT" query be
> sufficient? Because after that I have "FOR record IN SELECT .. LOOP" queries
> in some functions with some of the LOOP bodies being dynamic queries.
No. IIRC, you have to use EXECUTE on pretty much any query that
references the table, because any query that was run on the old foo_1
would have the old foo_1 cached. The other option is to use pltcl or
something which doesn't cache the plans I believe, but I don't know for
certain since I don't know tcl and haven't used it.
