From john@ecstreet.com Mon Mar  8 18:58:19 1999
Received: from ecstreet.com ([209.126.95.202])
	by hub.org (8.9.2/8.9.1) with SMTP id NAA14179
	for <pgsql-interfaces@postgresql.org>;
	Mon, 8 Mar 1999 13:58:19 -0500 (EST)
	(envelope-from john@ecstreet.com)
Received: (qmail 12909 invoked from network); 8 Mar 1999 07:00:33 -0000
Received: from dmarc.ecstreet.com (HELO dmarc) (209.126.95.205)
	by ecstreet.com with SMTP; 8 Mar 1999 07:00:33 -0000
Message-ID: <008801be6995$99e41da0$cd5f7ed1@dmarc.ecstreet.com>
From: "John Frailey" <john@ecstreet.com>
To: <pgsql-interfaces@postgresql.org>
Subject: multiple db connections
Date: Mon, 8 Mar 1999 12:58:06 -0600
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0085_01BE6963.4F1A3930"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.3155.0
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3155.0

This is a multi-part message in MIME format.

------=_NextPart_000_0085_01BE6963.4F1A3930
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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.=20

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

------=_NextPart_000_0085_01BE6963.4F1A3930
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>

<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>We are trying to port our pure java e-commerce =
software to=20
postgres. I am using database pooling with jdbc during an init stage of =
the main=20
servlet that dishes out the html content to the users. (we must use =
pooling,=20
because it takes too darn long to get a connection, and we need our web =
pages to=20
respond quickly)</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>My problem is that we use multiple databases per web =
request.=20
With other db's, I can make a connection to a central db, and then =
select ..=20
from &lt;database&gt;.&lt;table&gt; and make sure permissions are such, =
that I=20
can hop databases with just one connection I get from the pool. =
</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I can't find a similiar technique in postgres. Am I =
missing a=20
way to hop databases w/o having to create a new connection ?? It is =
adding 5x=20
the performance hit if I have to get a NEW connection everytime I need =
to hop. I=20
hate to have to create a connection pool for every database we have on =
our box.=20
(worried about memory consumption)</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Thanks ....</FONT></DIV></BODY></HTML>

------=_NextPart_000_0085_01BE6963.4F1A3930--




From urban@svenskatest.se Mon Mar  8 20:28:57 1999
Received: from fungus.svenskatest.se (fungus.svenskatest.se [195.67.21.83])
	by hub.org (8.9.2/8.9.1) with ESMTP id PAA28448
	for <pgsql-interfaces@postgreSQL.org>;
	Mon, 8 Mar 1999 15:28:57 -0500 (EST)
	(envelope-from urban@svenskatest.se)
Received: from cola.svenskatest.se (attafem.svenskatest.se [195.67.21.85])
	by fungus.svenskatest.se (8.9.3/8.9.3) with ESMTP id VAA25069;
	Mon, 8 Mar 1999 21:28:28 +0100 (MET)
Date: Mon, 8 Mar 1999 21:28:27 +0100 (CET)
From: Urban Widmark <urban@svenskatest.se>
To: John Frailey <john@ecstreet.com>
cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] multiple db connections
In-Reply-To: <008801be6995$99e41da0$cd5f7ed1@dmarc.ecstreet.com>
Message-ID: <Pine.LNX.4.10.9903082111060.1129-100000@cola.svenskatest.se>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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




From john@ecstreet.com Mon Mar  8 20:44:51 1999
Received: from ecstreet.com ([209.126.95.202])
	by hub.org (8.9.2/8.9.1) with SMTP id PAA30479
	for <pgsql-interfaces@postgreSQL.org>;
	Mon, 8 Mar 1999 15:44:51 -0500 (EST)
	(envelope-from john@ecstreet.com)
Received: (qmail 18989 invoked from network); 8 Mar 1999 08:47:06 -0000
Received: from dmarc.ecstreet.com (HELO dmarc) (209.126.95.205)
	by ecstreet.com with SMTP; 8 Mar 1999 08:47:06 -0000
Message-ID: <006901be69a4$7c8693a0$cd5f7ed1@dmarc.ecstreet.com>
From: "John Frailey" <john@ecstreet.com>
To: "Urban Widmark" <urban@svenskatest.se>
Cc: <pgsql-interfaces@postgreSQL.org>
Subject: Re: [INTERFACES] multiple db connections
Date: Mon, 8 Mar 1999 14:44:39 -0600
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.3155.0
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3155.0




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

Very well written db pool class can be obtained at
http://javaexchange.com/
has been very reliable for us... does what it needs too...

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

I suppose I am trying to get out of handling concurrency issues with
multiple threads. I went down this road with a pilot OODBMS (psepro not to
mention any names) and spent too much time (and code) worrying about
concurrency, and not enough time concentrating on buss. logic, and fell
behind on my projects. (what I heard from mgmt anyway!) So trying to take
advantage of a RDBMS's ability of handling multi-user. We usually have as
many as 50-100 threads running all times...

>Or is it a matter of security/transactions that says each user has to have
>his own connection?
>
>

We are using transactions when they commit to a purchase or subscription.

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

I like this option... do you know if there is a hard limit with how many
objects (schema.tables) I can have in a single database. I wouldn't think it
would be a reach to say we will have over 1000 or more in the near future if
we took this approach...

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