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.
FYI, setSchema is the correct way to change the search path. If you use that then we can deal with invalidating the cache. I just looked at the code and we don't currently.