agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedjdbc and postgresql function session question
4+ messages / 3 participants
[nested] [flat]
* jdbc and postgresql function session question
@ 2016-05-31 21:20 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Moore @ 2016-05-31 21:20 UTC (permalink / raw)
To: pgsql-sql
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\
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: jdbc and postgresql function session question
@ 2016-05-31 22:01 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2016-05-31 22:01 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: jdbc and postgresql function session question
@ 2016-05-31 22:36 David G. Johnston <david.g.johnston@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2016-05-31 22:36 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
On Tue, May 31, 2016 at 6:01 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> 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
https://www.postgresql.org/docs/current/static/sql-deallocate.html
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: jdbc and postgresql function session question
@ 2016-06-01 14:29 Michael Moore <michaeljmoore@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Moore @ 2016-06-01 14:29 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-sql
On Tue, May 31, 2016 at 3:36 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Tue, May 31, 2016 at 6:01 PM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>> 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
>
> https://www.postgresql.org/docs/current/static/sql-deallocate.html
>
> David J.
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-06-01 14:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-31 21:20 jdbc and postgresql function session question Michael Moore <michaeljmoore@gmail.com>
2016-05-31 22:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-05-31 22:36 ` David G. Johnston <david.g.johnston@gmail.com>
2016-06-01 14:29 ` Michael Moore <michaeljmoore@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox