Re: Mail an JDBC driver

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:
1) connect to the database and open schemaA. Set the search_path to schemaA
2) issue several SQL statements. They all go to schemaA (correct)
3) dump schemaA to a backup file (we call pg_dump as external process)
4) rename schemaA to schemaB (ALTER SCHEMA schemaA RENAME TO schemaB)
5) restore the backup (we call psql as external process) => now we've a duplicate of schemaA (but with another OID)
6) execute a "SHOW search_path". The search_path is still set to schemaA
7) issue another e.g.: DELETE SQL statement.
a. if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE...), the changes correctly were applied to schemaA
b. 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