Re: Enabling connection pooling by default

["Andrus" <kobruleht2(at)hot(dot)ee>]

Richard,

thank you.

> > Is connection pooling tested and works OK with unicode driver ?
> > Can pooling turned on by default in next driver release ?
>
> This is a good question. Until now, I was implementing my own
> connection pooling mechanism in my client application.

Why you re-invented the wheel?
ODBC driver manager in Windows has built-in pool manager probably.

> I have one question for you Andrus.  Are you trying to create a three
> tier application that uses ODBC on the second tier to handle
> connections for multiple third tier clients?   This would be a good
> use for connection pooling.

I have existing two-tier Windows GUI internet POS application which uses 
single connection.
Currently I open connection at appl startup and close it if user exits 
application.

If application is idle for a some time, ADSL modems or something other in 
wire closes connection automatically.
To prevent this I think I need to close connection after every major 
transaction is finished in my application.
In some cases major transactions are called rapidly. In this case automatic 
connection pool with 60 sec time should be useful to eliminate time
to establish new  connection.

Otherwize my code will be much more complicated: unit-of work cannot close 
connection. There must be some analyzer which closes connection
only after everything is completed. This requires much more refactoring of 
existing code. so I'm loooking for pool usage.

> In my case, I use a two tier approach.  When using only two tiers, I
> feel it is better if the client use only one connection that is opened
> and maintained throughout the client apps life-span.  In this scenario
> to many connections is only going to waste PG server memory resources
> but will not give the client any benefits that beyond what a single
> connection could due.

My POS appl life-span can be 24/7 and due to the connection auto-break issue 
holding connection open all this time seems to be not possible.
Currently I create automatically new connection if this occurs. This seems 
to cause 40 sec delay since appl tries to use dead connection first.

I have also one major issue with close connection approach: I need to 
determine how many clients are "logged-on" to current database at any 
moment.

Currently I use query

select usename,client_addr,MIN(backend_start) AS backend_start, COUNT(*) as 
cnt from
    pg_stat_activity where datname=?mydatabase
         GROUP BY 1,2 ORDER BY 2

I have no idea how to implement this with multiple connections.
Maybe to create login table. When user log-in appl inserts record to it, 
log-off removes record.

Major issue is that if client exists without logging off, wrong record 
remains in this table.
It is not possible to use some timeout since some clients may be logged-on 
for 24 hours.

Andrus.