Re: Mail an JDBC driver: Sample Code - Mailing list pgsql-jdbc
| From | KUNES Michael |
|---|---|
| Subject | Re: Mail an JDBC driver: Sample Code |
| Date | |
| Msg-id | 0B5AA3EC05A9C9438B5CC2B8A46AB60D011A9054E6@vie197nt Whole thread Raw |
| List | pgsql-jdbc |
Hi
one of my colleagues was so nice to create some test code that can be used for verification. See attachment.
The test case that fails in the described situation is testExceedThresholdBeforeCopy().
br
Michael
From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Mittwoch, 03. August 2016 15:25
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org; tgl@sss.pgh.pa.us; sitnikov.vladimir@gmail.com; david.g.johnston@gmail.com
Subject: Re: [JDBC] Mail an JDBC driver
Michael,
Based on Tom's response :
With the 9.2 database, if you cache a DELETE query as a prepared
statement, then it will retain the original search path and continue to
use that if the statement needs to be replanned. Moreover I'm pretty
sure that its notion of "original search path" was defined in terms of
schema OIDs not names, so that the table in the renamed schema would
continue to be targeted.
We got rid of that behavior precisely because it turned out to be more
surprising than useful ... but 9.2 is operating as designed. The apparent
dependency on JDBC version probably has to do with different driver
choices about whether/when to prepare the DELETE statement.
I think it would be wiser for you to upgrade (for many reasons) Is this possible ?
I don't think a test case is necessary we have a pretty good idea what is now happening.
Dave Cramer
davec@postgresintl.com
On 3 August 2016 at 09:22, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
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 J
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@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: Dienstag, 02. August 2016 15:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver
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.
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@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@postgresintl.com
Attachment
pgsql-jdbc by date: