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

[Thomas Hallgren]

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