[Apologies to Tom Lane. I was about to send this mail a third time
wondering why it was not appearing on the list when I notices my
replies were going to you. I had not set up pgsql-general as a "list"
in mutt. Sorry.]
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?
> 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.
> (2) instead of DROP/CREATE, consider DELETE FROM/INSERT INTO
> (or perhaps TRUNCATE instead of DELETE, although TRUNCATE can't be
> rolled back).
Not feasible, because some functions have dynamically created tables so
I need to re-create table at each invocation. Come to think of it,
EXECUTE around only the CREATE may not help because I have already
used it in such cases. Will check on Monday and revert to the list.
TIA
Regards,
Gaurav.
--
Sleep: A completely inadequate substitute for caffeine.
