Thread: multiple db connections
We are trying to port our pure java e-commerce software to postgres. I am using database pooling with jdbc during an init stage of the main servlet that dishes out the html content to the users. (we must use pooling, because it takes too darn long to get a connection, and we need our web pages to respond quickly)
My problem is that we use multiple databases per web request. With other db's, I can make a connection to a central db, and then select .. from <database>.<table> and make sure permissions are such, that I can hop databases with just one connection I get from the pool.
I can't find a similiar technique in postgres. Am I missing a way to hop databases w/o having to create a new connection ?? It is adding 5x the performance hit if I have to get a NEW connection everytime I need to hop. I hate to have to create a connection pool for every database we have on our box. (worried about memory consumption)
Thanks ....
On Mon, 8 Mar 1999, John Frailey wrote:
> We are trying to port our pure java e-commerce software to postgres. I
> am using database pooling with jdbc during an init stage of the main
> servlet that dishes out the html content to the users. (we must use
> pooling, because it takes too darn long to get a connection, and we
> need our web pages to respond quickly)
>
I've always wondered about connection pooling (yes, I understand you don't
want to reconnect on every query. I know I don't) Wouldn't it work just as
well using a single connection to the database? Assuming the database
connection is written to handle multiple requests (ie being thread safe).
Or is it a matter of security/transactions that says each user has to have
his own connection?
> My problem is that we use multiple databases per web request. With
> other db's, I can make a connection to a central db, and then select
> .. from <database>.<table> and make sure permissions are such, that I
> can hop databases with just one connection I get from the pool.
>
Usually (as far as I know) "select from database.table" is not different
databases but different schemas, all handled by the same database.
For example in DB2 or Oracle
"select from urban.small_table"
and
"select from john.small_table"
can all be in the database 'test', with urban.small_table and
john.small_table being different tables.
If you can switch from the 'urban' schema to the 'john' schema I don't
know (I think so). If postgres has schemas ... I'm not sure, but the JDBC
driver suggests it doesn't.
But schemas are just a namespace thing (or?), if it is access rights you
want they could be implemented by using different tables with different
rights (urban_some_table and john_some_table), no?
I'm sure I'm missing some great finesse here, so please enlighten me :)
/Urban
---
Urban Widmark urban@svenskatest.se
Svenska Test AB +46 90 71 71 23