Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
[SQL] jdbc and postgresql function session question
3 views
Skip to first unread message
Michael Moore
May 31, 2016, 5:21:45 PM5/31/16
to
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\
David G. Johnston
May 31, 2016, 6:36:24 PM5/31/16
to
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
Michael Moore
Jun 1, 2016, 10:30:36 AM6/1/16
to
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.
Thanks,Mike
Adrian Klaver
Jun 2, 2016, 9:00:38 PM6/2/16
to
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,
> 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?
> Mike\
>
--
Adrian Klaver
adrian...@aklaver.com
--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
0 new messages