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.
I will bet you that the exact number is 5..
That is when we will change your un-named prepared statement to a named prepared statement.