Re: Mail an JDBC driver

On 2 August 2016 at 09:19, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

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

So I just tried using prepared statements and wasn't able to duplicate
this. It would be really nice if you could test this against at recent
version of PostgreSQL, and provide us with a self contained test case?

Dave Cramer

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