From: thomas at tada.se (Thomas Hallgren) Date: Tue, 21 Mar 2006 08:44:14 +0100 Subject: [Pljava-dev] pljava reninitializing jdbc connection pool for every invocation In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF03C489B6@ca-mail1.cis.local> References: <6992E470F12A444BB787B5C937B9D4DF03C489B6@ca-mail1.cis.local> Message-ID: <441FAECE.6000807@tada.se> 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 >> >> > >