Re: Mail an JDBC driver - Mailing list pgsql-jdbc
| From | KUNES Michael |
|---|---|
| Subject | Re: Mail an JDBC driver |
| Date | |
| Msg-id | 0B5AA3EC05A9C9438B5CC2B8A46AB60D011A8FE5D8@vie197nt Whole thread Raw |
| Responses |
Re: Mail an JDBC driver
Re: Mail an JDBC driver Re: Mail an JDBC driver Re: Mail an JDBC driver |
| List | pgsql-jdbc |
Hi,
Maybe we found an issue in the JDBC drivers due to some change. Originally we used postgresql-9.2-1002.jdbc4.jar, now upgraded to postgresql-9.4.1208.jre7.jar. With the older version, everything worked as we expected, with the newer one we had a problem as described below.
The described algorithm was implemented, because we need to duplicate a schema and there is no “duplicate schema” command in PostgreSQL.
We did test and did NOT see the problem in
- postgresql-9.2-1002.jdbc4.jar
- postgresql-9.2-1004.jdbc4.jar
- postgresql-9.3-1103.jdbc4.jar
we could reproduce the described problem in
- postgresql-9.4-1202.jdbc4.jar
- postgresql-9.4-1204.jdbc4.jar
To be true, the use-case might seems “special”. Here is a description what we’ve done:
- connect to the database and open schemaA. Set the search_path to schemaA
- issue several SQL statements. They all go to schemaA (correct)
- dump schemaA to a backup file (we call pg_dump as external process)
- rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
- restore the backup (we call psql as external process) => now we’ve a duplicate of schemaA (but with another OID)
- execute a “SHOW search_path”. The search_path is still set to schemaA
- issue another e.g.: DELETE SQL statement.
- if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
- if we rely on the search_path, the changes are now applied to schemaB (e.g.: DELETE FROM table1 WHERE…). The SQL statement goes to the wrong schema!
We could reproduce the issue with about 10 DELETE statements before the schemaA was backup/rename/restored (exact number is hard to determine because of some DELETE CASCADE foreign constraints). But we can say that with 1-2 DELETE statements, we do not face the described problem.
Hint1: the connections to the DB are not closed during the whole algorithm
Hint2: all SQL statements in my info were executed with lower-case characters. Just made them Uppercase/CamelCase for better readability
One idea from our side is, that the schemaA had an OID in the beginning of our algorithm and this OID changes after the backup/rename/restore sequence.
In the changelog (https://jdbc.postgresql.org/documentation/changelog.html), we found some changes relating to the search_path at Version 9.4-1200 (2015-01-02). See the change from Author Alexis Meneses: “Setting the search_path from currentSchema property is done in startup packet (v3 protocol only)”
If you need more information, please let us know.
br
Michael Kunes
pgsql-jdbc by date: