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

From KUNES Michael
Subject Re: Mail an JDBC driver
Date
Msg-id 0B5AA3EC05A9C9438B5CC2B8A46AB60D011A8FED11@vie197nt
Whole thread Raw
In response to Re: Mail an JDBC driver  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-jdbc

hi,

 

first, thank you guys for the really quick answers J

 

Some additional infos

1.       it is the same PostgreSQL Server version for all tests: 9.2.4 (we also use 9.4.x but did all tests on the same server)

2.       the statement is quite simple: “delete from table where id=?” (id is a column with type “integer”). We use a prepared statement.

3.       since we could reproduce the problem with the new JDBC driver and could not reproduced it by just replacing the JAR with the older driver (we renamed the file and replaced it in the project), I would think it is a driver problem.
In addition, I executed a “show search_path” before we do all the DELETE statements and it was always at the same schema name (even in the situation where the deletions went to another schema). The search_path in our application always only has 1 schema name (no list)

4.       we change the search_path by executing a separate prepared Statement “set search_path to myschemaname” for the connection.

5.       Additional info: even by changing our statement from

delete from table where id=?”

to

set search_path to myschemaname;delete from table where id=?”

does not help. But it helps to change the statement to

delete from myschemaname.table where id=?”

                but since we’ve lots of such SQL statements in our code (I just outlined one of the situations), it’s not that easy to use a fully-qualified path at each and every position. Even worse for calling stored procedures that shall call another stored procedure in the same schema.

 

I’ll try to find out if something can be found in the logging by turning on statement logging but this may take same time.

 

A solution with something like a “refreshSearchPath()” method in the driver would also be a viable solution for us. Currently we’re evaluating if we could change all calls to fully-qualified SQL statements. And since the old driver also works fine for our application we will use this older driver version for the next time.

 

br

 Michael

 

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Dienstag, 02. August 2016 14:28
To: KUNES Michael
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Mail an JDBC driver

 

On Tue, Aug 2, 2016 at 3:31 AM, KUNES Michael <Michael.KUNES@frequentis.com> wrote:

If you need more information, please let us know.

 

​Is the same DB version (and which exact versions) being used in both tests?  I strongly suspect no...

 

Can you turn on full statement logging and see if anything unusual shows up?

 

​What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?

 

Having a self-contained test case makes the previous two items less problematic.  Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.

 

​David J.​

 

Could be because we now cache statements. I suspect the cached statement would not like you switching the schema out from under it.

How are you changing the search path? Ideally if you use setSchema we may have to look at invalidating the cache. Vladimir ?


On 2 August 2016 at 08:27, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Aug 2, 2016 at 3:31 AM, KUNES Michael <Michael.KUNES@frequentis.com> wrote:
If you need more information, please let us know.

​Is the same DB version (and which exact versions) being used in both tests?  I strongly suspect no...

Can you turn on full statement logging and see if anything unusual shows up?

​What's in your WHERE clause on the delete - and did you use Statement or PreparedStatement?

Having a self-contained test case makes the previous two items less problematic.  Also, that general test case can be translated to psql which would help to isolate the problem to either the driver or the database.

​David J.​


pgsql-jdbc by date:

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