Re: Set search_path + server-prepared statements = cached plan must not change result type

On Wed, Jan 20, 2016 at 10:23 AM, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
>>  I believe, and the conclusion was that
>>if you think you need this, you're doing it wrong
>
> So what is the recommended approach to use server-prepared statements
> at the client side (I mean at JDBC driver side)?
>
> Currently "prepare, switch search_path, execute" leads to "cached plan
> must not change result type" error.
> Can one expect the issue to be fixed in subsequent 8.4, 8.5, ..., 9.5 versions?

Are you really seeing the same behavior in all versions?  Because I
thought we changed this pretty significantly in this commit:

commit 0d5fbdc157a17abc379052f5099b1c29a33cebe2
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Fri Jan 25 14:14:41 2013 -0500
   Change plan caching to honor, not resist, changes in search_path.
   In the initial implementation of plan caching, we saved the active   search_path when a plan was first cached, then
reinstalledthat path   anytime we needed to reparse or replan.  The idea of that was to try to   reselect the same
referencedobjects, in somewhat the same way that views   continue to refer to the same objects in the face of schema or
name  changes.  Of course, that analogy doesn't bear close inspection, since   holding the search_path fixed doesn't
copewith object drops or renames.   Moreover sticking with the old path seems to create more surprises than   it
avoids. So instead of doing that, consider that the cached plan depends   on search_path, and force reparse/replan if
theactive search_path is   different than it was when we last saved the plan.
 
   This gets us fairly close to having "transparency" of plan caching, in the   sense that the cached statement acts
thesame as if you'd just resubmitted   the original query text for another execution.  There are still some corner
caseswhere this fails though: a new object added in the search path   schema(s) might capture a reference in the query
text,but we'd not realize   that and force a reparse.  We might try to fix that in the future, but for   the moment it
lookstoo expensive and complicated.
 

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company