Hi,
There's a not so well known issue of "set search_path" and
"server-prepared statement" usage.
In short, it does not always work. See more details in [1].
There issue has reproduced once again recently (see [2], that explains
that search_path is modified for a multi-tenant setup).
Even though I wish that kind of details were handled at the backend
level (see Tom's example in [1] when "create table" should invalidate
the statement), we need to do something about that with existing PG
versions. Otherwise it is another "never modify search_path" or "never
use server-prepared" catch-22.
Here's my question: why change in search_path does NOT generate
ParameterStatus message from the backend?
I thought I could capture ParameterStatus events, and use
per-search_path cache at the JDBC level. However that does not seem to
work.
Here's what I get with 9.5rc1:
simple execute, maxRows=0, fetchSize=0, flags=17
FE=> Parse(stmt=null,query="SET search_path TO "$user",public,schema2",oids={})
FE=> Bind(stmt=null,portal=null)
FE=> Describe(portal=null)
FE=> Execute(portal=null,limit=0)
FE=> Sync
<=BE ParseComplete [null]
<=BE BindComplete [unnamed]
<=BE NoData
<=BE CommandStatus(SET)
<=BE ReadyForQuery(I)
Am I missing something?
[1]: http://www.postgresql.org/message-id/22921.1358876659@sss.pgh.pa.us
[2]: https://github.com/pgjdbc/pgjdbc/issues/496
Vladimir Sitnikov