Re: 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(at)postgresintl(dot)com
www.postgresintl.com

On 3 August 2016 at 09:22, KUNES Michael <Michael(dot)KUNES(at)frequentis(dot)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(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> 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.
>
> 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
>
> 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
>
>
>