agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
5+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
@ 2006-03-21 06:56
0 siblings, 1 reply; 5+ messages in thread
From: @ 2006-03-21 06:56 UTC (permalink / raw)
Hi
Following is the scenario:
A plpgsql function calls a java method using pljava. The initialization
of the jdbc pool seems to be happening with every call to the
getConnection method.
Is there a way to implement singletons within pljava
This is the java code that initializes the pool
private static Jdbc3PoolingDataSource pool;
private static Logger logger = Logger.getLogger(PostgresAdapter.class);
private static PropertyResourceBundle resourceBundle;
public static void setupPool() throws Exception
{
logger.info("***setting up postgres
pool");
PropertyResourceBundle
resourceBundle = null;
resourceBundle = new
PropertyResourceBundle(new FileInputStream("postgres-ds.properties"));
pool = new Jdbc3PoolingDataSource();
logger.info("***finished setting up
postgres pool");
pool.setServerName(resourceBundle.getString("serverName"));
pool.setPortNumber(Integer.parseInt(resourceBundle.getString("portNumber
")));
pool.setDatabaseName(resourceBundle.getString("databaseName"));
pool.setUser(resourceBundle.getString("user"));
pool.setPassword(resourceBundle.getString("password"));
pool.setMaxConnections(25);
}
public static Connection getConnection() throws
Exception {
if(pool == null) {
setupPool();
}
Connection con =
pool.getConnection();
con.setAutoCommit(false);
return con;
}
Many thx in advance for answers
Sriram
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20060320/3c1ff720/attachment.html;
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
@ 2006-03-21 07:14
parent:
0 siblings, 0 replies; 5+ messages in thread
From: @ 2006-03-21 07:14 UTC (permalink / raw)
Hi Sriram,
PostgreSQL spawns a new backend process each time you make a connection.
PL/Java will initialize a Java Virtual Machine instance the first time
you call a PL/Java function using a connection. The JVM becomes a part
of the backend process and its lifespan is thus equal to the lifespan of
your connection. So while its indeed possible to create singletons in
PL/Java, such a singleton is per connection.
From the looks of it, you are trying to set up a pool of DataSources
that will allow you to connect to other PostgreSQL instances from within
PL/Java. I'm a bit curious why you would like to do that. Can you shed
some light on what it is you want to do? Perhaps I can help suggesting
an alternative.
Kind Regards,
Thomas Hallgren
Sriram Dandapani wrote:
>
> Hi
>
>
>
> Following is the scenario:
>
>
>
> A plpgsql function calls a java method using pljava. The
> initialization of the jdbc pool seems to be happening with every call
> to the getConnection method.
>
> Is there a way to implement singletons within pljava
>
>
>
> This is the java code that initializes the pool
>
>
>
> private static Jdbc3PoolingDataSource pool;
>
> private static Logger logger = Logger.getLogger(PostgresAdapter.class);
>
> private static PropertyResourceBundle resourceBundle;
>
>
>
> public static void setupPool() throws Exception {
>
> logger.info("***setting up
> postgres pool");
>
> PropertyResourceBundle
> resourceBundle = null;
>
> resourceBundle = new
> PropertyResourceBundle(new FileInputStream("postgres-ds.properties"));
>
> pool = new Jdbc3PoolingDataSource();
>
> logger.info("***finished setting
> up postgres pool");
>
>
> pool.setServerName(resourceBundle.getString("serverName"));
>
>
> pool.setPortNumber(Integer.parseInt(resourceBundle.getString("portNumber")));
>
>
> pool.setDatabaseName(resourceBundle.getString("databaseName"));
>
>
> pool.setUser(resourceBundle.getString("user"));
>
>
> pool.setPassword(resourceBundle.getString("password"));
>
> pool.setMaxConnections(25);
>
> }
>
>
>
> public static Connection getConnection()
> throws Exception {
>
> if(pool == null) {
>
> setupPool();
>
> }
>
> Connection con = pool.getConnection();
>
> con.setAutoCommit(false);
>
> return con;
>
> }
>
>
>
> Many thx in advance for answers
>
>
>
> Sriram
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
@ 2006-03-21 07:44
0 siblings, 0 replies; 5+ messages in thread
From: @ 2006-03-21 07:44 UTC (permalink / raw)
Sriram Dandapani wrote:
> We are migrating autonomous transactions from Oracle to Postgres. The
> only way to achieve independent commits within postgres functions is to
> call out to external programs, hence the pljava call.
>
>
Ok. I understand.
> I need a way for java calls to use jdbc connections from a pool. If you
> can shed some light on how to do that, I would really appreciate it
>
What would the scope of that pool be?
Keep in mind that the JVM maintained in a PostgreSQL connection is
exclusive to that connection. In my mind, that makes using a connection
pool from inside a PL/Java function redundant. There will never be
multiple users of it anyway. Maintaining one single remote connection is
just as good. Also keep in mind that PL/Java in itself lives in a
process dedicated to a connection. If your original PostgreSQL
connections are pooled, then your PL/Java instances will be pooled too
(and hence the single remote connection that it might maintain).
Regards,
Thomas Hallgren
> Thanks
>
> Sriram
>
> -----Original Message-----
> From: Thomas Hallgren [mailto:thomas at tada.se]
> Sent: Monday, March 20, 2006 11:15 PM
> To: Sriram Dandapani
> Cc: pljava-dev at gborg.postgresql.org
> Subject: Re: [Pljava-dev] pljava reninitializing jdbc connection pool
> for every invocation
>
> Hi Sriram,
> PostgreSQL spawns a new backend process each time you make a connection.
>
> PL/Java will initialize a Java Virtual Machine instance the first time
> you call a PL/Java function using a connection. The JVM becomes a part
> of the backend process and its lifespan is thus equal to the lifespan of
>
> your connection. So while its indeed possible to create singletons in
> PL/Java, such a singleton is per connection.
>
> From the looks of it, you are trying to set up a pool of DataSources
> that will allow you to connect to other PostgreSQL instances from within
>
> PL/Java. I'm a bit curious why you would like to do that. Can you shed
> some light on what it is you want to do? Perhaps I can help suggesting
> an alternative.
>
> Kind Regards,
> Thomas Hallgren
>
> Sriram Dandapani wrote:
>
>> Hi
>>
>>
>>
>> Following is the scenario:
>>
>>
>>
>> A plpgsql function calls a java method using pljava. The
>> initialization of the jdbc pool seems to be happening with every call
>> to the getConnection method.
>>
>> Is there a way to implement singletons within pljava
>>
>>
>>
>> This is the java code that initializes the pool
>>
>>
>>
>> private static Jdbc3PoolingDataSource pool;
>>
>> private static Logger logger =
>>
> Logger.getLogger(PostgresAdapter.class);
>
>> private static PropertyResourceBundle resourceBundle;
>>
>>
>>
>> public static void setupPool() throws
>>
> Exception {
>
>> logger.info("***setting up
>> postgres pool");
>>
>> PropertyResourceBundle
>> resourceBundle = null;
>>
>> resourceBundle = new
>> PropertyResourceBundle(new FileInputStream("postgres-ds.properties"));
>>
>> pool = new
>>
> Jdbc3PoolingDataSource();
>
>> logger.info("***finished setting
>> up postgres pool");
>>
>>
>> pool.setServerName(resourceBundle.getString("serverName"));
>>
>>
>>
>>
> pool.setPortNumber(Integer.parseInt(resourceBundle.getString("portNumber
> ")));
>
>>
>> pool.setDatabaseName(resourceBundle.getString("databaseName"));
>>
>>
>> pool.setUser(resourceBundle.getString("user"));
>>
>>
>> pool.setPassword(resourceBundle.getString("password"));
>>
>> pool.setMaxConnections(25);
>>
>> }
>>
>>
>>
>> public static Connection getConnection()
>> throws Exception {
>>
>> if(pool == null) {
>>
>> setupPool();
>>
>> }
>>
>> Connection con =
>>
> pool.getConnection();
>
>> con.setAutoCommit(false);
>>
>> return con;
>>
>> }
>>
>>
>>
>> Many thx in advance for answers
>>
>>
>>
>> Sriram
>>
>>
>>
>>
>>
> ------------------------------------------------------------------------
>
>> _______________________________________________
>> Pljava-dev mailing list
>> Pljava-dev at gborg.postgresql.org
>> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>>
>>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
@ 2006-03-21 18:19
0 siblings, 0 replies; 5+ messages in thread
From: @ 2006-03-21 18:19 UTC (permalink / raw)
Sriram Dandapani wrote:
> How thread-safe is this connection. If I were to use just 1 connection,
> does pljava guarantee safe concurrent access
>
>
There will never be concurrent access to a connection. PostgreSQL
guarantees that, not PL/Java.
> Which begs the question:
>
> Does every call to pljava function create a separate jvm...
No, of course not. It's one JVM *per connection*. You can have millions
of calls per connection. My point is, they are never parallel so you
have absolutely no use of a pool from within a PostgreSQL function (be
it Java or some other language).
> Lets say
> there are cron jobs that are kicked off periodically and lots of
> postgres functions are invoked. Each function calls the pljava function
> several times. What is the overhead in terms of jvm startup/connection
> creation?
>
>
Each new connection spawns a new JVM. During the life of the connection,
each call is extremely efficient (due to in-process calls).
> Does the java function that obtains the connection need to worry about
> all this or is a simple jdbc connection creation mechanism sufficient. I
> am not familiar with pljava internals.
>
>
I'm sorry. I don't understand this question. Worry about what exactly?
> Also, do we need to use the postgresql.jar file or will just pljava be
> sufficient
>
>
PL/Java has its own JDBC driver. This driver is special in that it will
connect you to the current transaction (the transaction in which the
PL/Java function call was made). Each call you make using this driver
will execute within the scope of the current transaction. If your
objective is to have autonomous transactions that execute independently
of the one that is current, then you must connect to another session,
i.e. another backend process. That in turn, will require you to use the
client jdbc driver (i.e. the postgresql.jar).
Regards,
Thomas Hallgren
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
@ 2006-03-21 18:37
0 siblings, 0 replies; 5+ messages in thread
From: @ 2006-03-21 18:37 UTC (permalink / raw)
Sriram Dandapani wrote:
> Ok..the picture is getting clearer.. This is what we do
>
> The java method that is called using pljava obtains a jdbc connection
> using the postgresql driver because we need the autonomous transaction
> feature.
>
> When a postgres function calls this pljava function, every invocation
> results in a new connection being obtained. Since we do not use
> pljava.jar to obtain this connection, am I losing out on the nice
> in-process features you mentioned.
>
>
Well, you can never have autonomous transactions in-process when you use
PostgreSQL. But there's no need for you to create a new remote
connection for each and every invocation. Let it be created in your
first call. Store it in a static variable and reuse it on all subsequent
calls. Make sure each call performs a commit or rollback on it but don't
close it.
Regards,
Thomas Hallgren
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-21 18:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-21 06:56 [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
2006-03-21 07:14 ` [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
2006-03-21 07:44 [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
2006-03-21 18:19 [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
2006-03-21 18:37 [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox