Re: Mail an JDBC driver - Mailing list pgsql-jdbc
| From | KUNES Michael |
|---|---|
| Subject | Re: Mail an JDBC driver |
| Date | |
| Msg-id | 0B5AA3EC05A9C9438B5CC2B8A46AB60D011A8FFA5D@vie197nt Whole thread Raw |
| In response to | Re: Mail an JDBC driver (Vladimir Sitnikov <sitnikov.vladimir@gmail.com>) |
| Responses |
Re: Mail an JDBC driver
Re: Mail an JDBC driver |
| List | pgsql-jdbc |
Hi,
we change the schema name by extending the file that we create via pg_dump.
- we execute pg_dump to dump the schema into a temp-file
- then we create a new temp file with
o BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
o DROP SCHEMA IF EXISTS " + destinationSchemaName + " CASCADE;"
o ALTER SCHEMA " + sourceSchemaName + " RENAME TO " + destinationSchemaName + ';'
o append the contents from pg_dump
o COMMIT;
- this new temp-file is then restored by spawning psql
At the moment we switched back to the older driver what is 100% fine for our purposes.
And we’re thinking about adapting our SW to
a) always using fully-qualified requests (including some code-cleanup)
b) changing the whole dump/restore algorithm to avoid the renaming of the schema (i.e.: dump schema -> adapt dumped file -> restore schema under its new name)
Even if the solution from our side worked fine for some time, I think we can also improve at our side to get rid of this special use case.
We wanted to inform you mostly to give as much info to improve the driver also in the future J
br
Michael
From: Vladimir Sitnikov [mailto:sitnikov.vladimir@gmail.com]
Sent: Dienstag, 02. August 2016 16:23
To: Dave Cramer; KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] 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
pgsql-jdbc by date: