Re: Mail an JDBC driver

On 2 August 2016 at 03:31, KUNES Michael <Michael(dot)KUNES(at)frequentis(dot)com>
wrote:

> 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.
>
>
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.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com