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.

1. if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
2. 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.