Re: Prepared statements fail after schema changes with surprising error

Peter van Hardenberg <pvh@pvh.ca> writes:
> Okay - I've narrowed it down to an interaction with schema recreation.
> Here's a minimal test-case I created by paring back the restore from the
> pg_restore output until I only had the essence remaining:

Hm ... I'm too tired to trace through the code to prove this theory, but
I think what's happening is that this bit:

> DROP SCHEMA public;
> CREATE SCHEMA public;

changes the OID of schema public, whereas the search_path that's cached
for the cached plan is cached in terms of OIDs.  So while there is a
table named public.z1 at the end of the sequence, it's not in any schema
found in the cached search path.

We could possibly fix that by making the path be cached as textual names
not OIDs, but then people would complain (rightly, I think) that
renaming a schema caused unexpected behavior.

There's also the other issues that have been discussed again recently
about whether we should be attempting to reinstall an old search path in
the first place.  We could easily dodge this issue if we redefined what
the desired behavior is ... but I'm not sure if we want to risk the
fallout of that.
        regards, tom lane