Re: Enabling connection pooling by default - Mailing list pgsql-odbc

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.