[Pljava-dev] pljava reninitializing jdbc connection pool for every invocation

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
>>
>>
>
>