RE: Connection Pooling...(Repost)...please do help.. - Mailing list pgsql-interfaces

I haven't found PG to have much connection overhead, why would open/closing
a connection-per-query require server side connection pooling?  You might
try having your application acquire a connection on-demand and then having a
timeout mechanism that discards the connection after a certain amount of
idle time.  That if you have to fire off hundreds of small queries you don't
encounter connection overhead.  If the machine is idle, it disconnects and
the server doesn't get loaded down with tons of idle backends.

This may sound overly complicated, but you might move the database
operations out of the client-tier and into a application-tier.  DCOM would
allow you to query from the COM layer and broker objects containing the
results back to the client.  This way the middle tier could maintain it's
own pool of connections iternally, plus you then get to take advantage of
the other benefits of a distributed system.

As a side note, I have a little research project going right now.  It is an
HTTP XML Server that acts as a database liason by accepting HTTP POST
requests (USER, PASSWORD, SQL, etc) and returning the results as XML.  It is
Java based, but you could do it with any language you want.  Coming from a
Win32 client, one could use IE 5's XML parser to process the results.  You
could implement something very similar in a short amount of time.  (This is
partially up-and-running with less than a week's work)  This is a perfect
place to implement connection pooling (which happens to be what I am
currently adding to it).  I would be glad to provide source once I get it
stabilzed, although I don't know if it will ever be mature enough for
production work...I just wanted to learn XML.  :)

Anyway, I just wanted to throw some ideas out there...
Joel

-----Original Message-----
From: sk@pobox.com [mailto:sk@pobox.com]
Sent: Friday, December 15, 2000 11:32 AM
To: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Connection Pooling...(Repost)...please do
help..


Thanks Joel,

Yes there is. But it only keeps your unused connections on for a given
amount of time, so as to avoid opening new connections when you open a
connection again within the stipulated time.

In our case, we keep the connection open till the application has any
forms open. Problem in this case is that multiple users from different
workstations are opening connections on the PG server on RH 6.2.

What we want to do is start closing connections from applications
after query and somehow pool the connections on the Linux server.

Would like comments on this strategy and a how-to on this....help?
anyone?

With best regards.

Sanjay.

On Wed, 13 Dec 2000 15:19:53 -0500, in
tci.lists.rdbms.postgresql.interfaces you wrote:

>I know that the ODBC "engine" on Win32-based systems is capable of
>performing connection pooling, but I am not sure if the driver has to
>support it or not...anyone?  Check the "Connection Pooling" tab on the ODBC
>admin window in control panel.  If you don't have one, you might try
>upgrading MDAC.
>
>Joel
>
>-----Original Message-----
>From: Sanjay Arora [mailto:sk@pobox.com]
>Sent: Wednesday, December 13, 2000 7:19 PM
>To: pgsql-interfaces@postgresql.org
>Subject: [INTERFACES] Connection Pooling...(Repost)...please do help...
>
>
>I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
>Application accessing the DB through postgrSQL ODBC driver v. 6.50.
>
>I want to pool my connections on the postgreSQL server. Can some people
>give me some pointers? Some Web Resources for studying this subject? My
>experience on connection pooling is limited to MTS in MS environs.
>
>With best regards.
>
>Sanjay.
>