Re: Mail an JDBC driver

hi Dave,

we can try to make a self-contained test, but this may take some time. We’ve to extract this from our software into a separate project.
Vice versa I can offer to try a test version of a JDBC driver in our environment if you want.
In the meantime we tried this

Connection con = …
conn.unwrap(PGConnection.class).setPrepareThreshold(10);

With this adaptation, the problem is after deletion number 10. So your bet seems to be 100% correct ☺
But I fear, if we set the Threshold to e.g. Integer.MAX_VALUE this will slow down the performance?

br
Michael

P.S.: hope it is ok that I included the other mail-contributors to my reply. If not, please tell me and I’ll just reply to the mailing list and the sender of the mail.

From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf Of Dave Cramer
Sent: Dienstag, 02. August 2016 15:28
To: KUNES Michael
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Mail an JDBC driver

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

On 2 August 2016 at 03:31, KUNES Michael <Michael(dot)KUNES(at)frequentis(dot)com<mailto: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.
a. if it is fully qualified (e.g.: DELETE FROM schemaA.table1 WHERE…), the changes correctly were applied to schemaA
b. 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<mailto:davec(at)postgresintl(dot)com>
www.postgresintl.com<http://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<mailto:davec(at)postgresintl(dot)com>
www.postgresintl.com<http://www.postgresintl.com/>