Re: Prepared statements fail after schema changes with surprising error

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Instead of
> re-establishing the original search_path in a rather vain hope that the
> same objects will be re-selected by parse analysis, we should consider
> that the prepared query has a dependency on the active search path, and
> thus force a replan if the effective search path changes.

Presuming that this flows through to SPI and in effect pl/pgsql, this is
exactly what I was arguing for a while back, when we ran into cases with
connection pooling where the plans generated by a pl/pgsql function
remained the same, referring to the objects against which it was
originally planned, even though the search_path had changed.  As I
recall, the same might have even been true across 'set role' actions
where the text of 'search_path' wasn't actually changed, but the '$user'
variable inside it was.

Now, there is definitely legitimate concern about search_path rejiggery
and security definer functions, so nothing done here should change how
we handle that case.

> Consider for example that the search path is a, b, c,
> and we have a prepared query "select * from t", and that currently
> refers to b.t.  If now someone creates a.t, or renames a.x to a.t,
> then a replan would cause the query to select from a.t ... but there
> was no invalidation event that will impinge on the stored plan, and the
> search_path setting didn't change either.  I don't think we want to
> accept the overhead of saying "any DDL anywhere invalidates all cached
> plans", so I don't see any good way to make this case transparent.
> How much do we care?

That may simply be a trade-off that we need to make.  I agree that we
don't want to invalidate everything due to any DDL anywhere.  I do think
that what you're proposing here wrt invalidating based on search_path
changes is an improvement over the current situation.
Thanks,
    Stephen