Re: Mail an JDBC driver - Mailing list pgsql-jdbc

From Dave Cramer
Subject Re: Mail an JDBC driver
Date
Msg-id CADK3HH+dBHU54QvRF9wfkSQWB69GAv6S6GobMxWOC-T8f+4rmA@mail.gmail.com
Whole thread Raw
In response to Re: Mail an JDBC driver  (Dave Cramer <pg@fastcrypt.com>)
Responses Re: Mail an JDBC driver  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Mail an JDBC driver  (Vladimir Sitnikov <sitnikov.vladimir@gmail.com>)
Re: Mail an JDBC driver  (KUNES Michael <Michael.KUNES@frequentis.com>)
Re: Mail an JDBC driver  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Mail an JDBC driver  (Vladimir Sitnikov <sitnikov.vladimir@gmail.com>)
Re: Mail an JDBC driver  (KUNES Michael <Michael.KUNES@frequentis.com>)
List pgsql-jdbc
On 2 August 2016 at 09:19, Dave Cramer <pg@fastcrypt.com> wrote:



On 2 August 2016 at 03:31, KUNES Michael <Michael.KUNES@frequentis.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.


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?

pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: Mail an JDBC driver
Next
From: Tom Lane
Date:
Subject: Re: Mail an JDBC driver