Re: "Relation x does not exist" error when x does exist

[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.