Quick Links

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

From:	Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
To:	Robert Haas <robertmhaas(at)gmail(dot)com>
Cc:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: Set search_path + server-prepared statements = cached plan must not change result type
Date:	2016-01-21 20:55:43
Message-ID:	CAB=Je-Gt3uWAm2=OD=VFGoq1Qp00yQbmaXzAG-cbMF4BGbn0jA@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-hackers

Robert>Are you really seeing the same behavior in all versions?

I do not have "pre 9.1" at hand, however all 9.1, 9.2, 9.3, 9.4, and
9.5 are affected.

9.1 just silently executes "old statement" as if search_path was not
modified at all.
9.2, 9.3, 9.4, and 9.5 all fail with "cached plan must not change
result type" error.

See java-based test in [1], and build logs for 9.1-9.4 in [2]

I do not have "brand new 9.5", however I think 9.5rc1 is good enough:
"PostgreSQL 9.5rc1 on x86_64-apple-darwin15.2.0, compiled by Apple
LLVM version 7.0.0 (clang-700.1.76), 64-bit"

Here's my test case:

select version();

create schema customer1;
create table customer1.test(i int4);

create schema customer2;
create table customer2.test(i varchar);

set search_path to customer1,public;
prepare stmt as select * from test;

set search_path to customer2,public;

execute stmt;

--ERROR: cached plan must not change result type
--SQL state: 0A000

[1]: https://github.com/pgjdbc/pgjdbc/commit/8fcd07a24666de308419d54e49e2f65f40661e2a#diff-526a72847ed4c9f31f699515d06e508bR188
[2]: https://travis-ci.org/pgjdbc/pgjdbc/builds/103940843

Vladimir