Обсуждение: jdbc and postgresql function session question
When the postgres jdbc driver calls a postgres function, does it get a new session every time? If I create a PREPARED statement on the first call, will it still be available on the 2nd call? If not, is there any way I could get the prepared statement to be used on successive calls?
TIA,
Mike\
On 05/31/2016 02:20 PM, Michael Moore wrote: > When the postgres jdbc driver calls a postgres function, does it get a > new session every time? If I create a PREPARED statement on the first > call, will it still be available on the 2nd call? If not, is there any > way I could get the prepared statement to be used on successive calls? I think this is going to need more explanation, so: 1) When you say session are you talking about connection or a transaction? A function will run in its own transaction each time, but can be run multiple times in a connection. 2) Where is the PREPARED statement being built, in the function or outside it? 3) Can show an outline of what you are doing? > TIA, > Mike\ > -- Adrian Klaver adrian.klaver@aklaver.com
On 05/31/2016 02:20 PM, Michael Moore wrote:When the postgres jdbc driver calls a postgres function, does it get a
new session every time? If I create a PREPARED statement on the first
call, will it still be available on the 2nd call? If not, is there any
way I could get the prepared statement to be used on successive calls?
I think this is going to need more explanation, so:
1) When you say session are you talking about connection or a transaction? A function will run in its own transaction each time, but can be run multiple times in a connection.
2) Where is the PREPARED statement being built, in the function or outside it?
3) Can show an outline of what you are doing?
Making some assumptions...
con = DriverManager.getConnection(); -- opens a database session
Any JDBC Statement objects you create using this con are able to access named PostgreSQL PREPAREd statement previously created.
However, beware of
DEALLOCATE ALL[1]
While you are unlikely to issue such a command explicitly you need to be aware that connection poolers may do so in some configurations.
CallableStatement stmt = con.prepareCall(sql); --sees any session-scoped data since con was created.
con.close();
-- now the database session is done
On Tue, May 31, 2016 at 3:36 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
A little background. This is a PL/SQL to pgplsql conversion. A Java process calls a pgsql function. The function does a few table lookups and builds a moderately complex (3 way join) SQL SELECT statement and executes it dynamically returning a set of about 5 rows. There are about 20 possible structural variations of this same select statement. By that I mean different columns being referenced in the WHERE clause and possibly a 4th table being joined. There are no skewed data statistics, so it is unlikely that the same select statement with different predicate values will choose a different plan. This happens about about 10 times per second so performance is critical. I am trying to determine if there could be performance gains by having my pgsql function execute a PREPARE statement. I am going to study the information at David's link and get information about how java is handling the connection then I should be able to come back here and ask more specific questions. On 05/31/2016 02:20 PM, Michael Moore wrote:When the postgres jdbc driver calls a postgres function, does it get a
new session every time? If I create a PREPARED statement on the first
call, will it still be available on the 2nd call? If not, is there any
way I could get the prepared statement to be used on successive calls?
I think this is going to need more explanation, so:
1) When you say session are you talking about connection or a transaction? A function will run in its own transaction each time, but can be run multiple times in a connection.
2) Where is the PREPARED statement being built, in the function or outside it?
3) Can show an outline of what you are doing?Making some assumptions...con = DriverManager.getConnection(); -- opens a database sessionAny JDBC Statement objects you create using this con are able to access named PostgreSQL PREPAREd statement previously created.However, beware ofDEALLOCATE ALL[1]While you are unlikely to issue such a command explicitly you need to be aware that connection poolers may do so in some configurations.CallableStatement stmt = con.prepareCall(sql); --sees any session-scoped data since con was created.con.close(); -- now the database session is done
Thanks,Mike