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

Gaurav Priyolkar <gaurav_lists@yahoo.com> writes:
> So as you can see, I get a "relation does not exist" error on a
> function that is very much there.=20

It's not there at the point of the failure, though.  Your observation
that foo_1 is still there is made after rolling back the drop and
recreate of foo_1.

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.

Workarounds: (1) use EXECUTE to avoid caching a plan for the problem
queries; (2) instead of DROP/CREATE, consider DELETE FROM/INSERT INTO
(or perhaps TRUNCATE instead of DELETE, although TRUNCATE can't be
rolled back).

            regards, tom lane