Re: Mail an JDBC driver
| От | Vladimir Sitnikov |
|---|---|
| Тема | Re: Mail an JDBC driver |
| Дата | |
| Msg-id | CAB=Je-HD9GiuYgKeaz+CWh+zfehZx6ojNaPeJp2yrPSU__S9dQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Mail an JDBC driver (Dave Cramer <pg@fastcrypt.com>) |
| Ответы |
Re: Mail an JDBC driver
|
| Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: