Re: Mail an JDBC driver

Apologies for the long read.
TL;DR: S1, S2, S3 are my proposed solutions.
I would like to hear feedback on S3 if that is the only thing you would
read below.

KUNES Michael> rename *schemaA* to *schemaB* (ALTER SCHEMA schemaA RENAME
TO schemaB)

Michael,
Are you issuing "alter schema rename" via pgjdbc or via psql?

I think current issue is related to
https://github.com/pgjdbc/pgjdbc/issues/496 (and hackers thread inside:
http://www.postgresql.org/message-id/22921.1358876659@sss.pgh.pa.us )

To my best knowledge client side has no idea if there was a schema change
(e.g. alter schema rename, alter table rename, set search_path, etc), so
from client perspective, "cache invalidation" is a non-trivial task.

On top of that, PostgreSQL itself has no easy way to tell when the
statements need be re-parsed in a generic case. Basically, any DDL can
cause statement invalidation (see Tom's example in the hackers link above)

S1) The simplest solution would be to reset the connection pool right after
schema duplication.

S2) Second option is to issue a "deallocate all" request via executeUpdate
kind of call, however you would have to issue that in each and every
connection, and you don't want to "deallocate all" often as it will hurt
the performance. Note: "deallocate all" is not yet supported by pgjdbc,
however it is not a rocket science.

S3) I wonder if LISTEN/NOTIFY could be reused to track/invalidate statement
cache.
For instance, each pgjdbc connection subscribes to
"pgjdbc_statement_invalidate" channel. When someone wants to reset the
cache, he issues "notify" on the specific channel, and that is propagated
to the relevant clients. For instance, as sysadmin did "alter ...", he
could issue "notify..." statement and that would transparently renew the
statements for all connected pgjdbc clients.

Dave>FYI, setSchema is the correct way to change the search path

Technically speaking, I'm not sure if we should invalidate the cache on
each and every `setSchema` call.
1) Why invalidate the cache if application is issuing setSchema with
exactly the same schema again and again?
2) setSchema does not support multiple schemas on the path, so for complex
paths applications would have to resort to execute...("set ...")

Personally speaking, I wish search_path to be a GUC_REPORT. That is server
should send notifications when the value changes over time. Of course "full
cache invalidation on search_path change" is not optimal, however the
changes should not be often, and that would provide at least some solution
to the "wrong statement executed" or "statement executed in the wrong
schema" problem.

Vladimir