Enabling connection pooling by default

Users can install 08.03.0200 ODBC driver in Windows using official msi
package.

"Set connection spooling attributes" window in Windows Vista ODBC Data
Source Administrator dialog shows that connection pooling is turned off by
default.

How to turn it on during default installation or programmatically so that
user intraction is not required ?
Is it possible to usie some connection string option to turn pooling on ?

For SQL and Oracle ODBC drivers connection pooling is turned on by default.

Andrus.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

On Wed, Oct 15, 2008 at 7:16 AM, Andrus wrote:

> How to turn it on during default installation or programmatically so that
> user intraction is not required ?
> Is it possible to usie some connection string option to turn pooling on ?

This is the official list of options that can be passed to the driver.
Unfortunately I didn't see any options for connection pooling:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlo dbc/psqlodbc/docs/config-opt.html?rev=1.1&content-type=text/ plain


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Richard,

thank you.

Maybe connection pooling is ODBC driver manager function, not ODBC driver
itself.
In this case ODBC driver manager should have some special setting in
connection string like Driver=
which applies for all drivers.

Why connection pooling is not turned ON by msi installer ?
Maybe there is some command line option or is it possible to write to
registry to turn in on.
I do'nt like the idea to force end users to set this.
How to check whether conn pooling is active and issue warning to user if not
?

Andrus.


----- Original Message ----- >
This is the official list of options that can be passed to the driver.
> Unfortunately I didn't see any options for connection pooling:
>
> http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlo dbc/psqlodbc/docs/config-opt.html?rev=1.1&content-type=text/ plain


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Hello,

I have discovered this problem a little. It seems to me installer has to
set "CPTimeout" registry key to some integer value. This will enable
pooling by default. Here is this registry key:

HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_na me\CPTimeout


Andrus wrote:
> Richard,
>
> thank you.
>
> Maybe connection pooling is ODBC driver manager function, not ODBC
> driver itself.
> In this case ODBC driver manager should have some special setting in
> connection string like Driver=
> which applies for all drivers.
>
> Why connection pooling is not turned ON by msi installer ?
> Maybe there is some command line option or is it possible to write to
> registry to turn in on.
> I do'nt like the idea to force end users to set this.
> How to check whether conn pooling is active and issue warning to user
> if not ?
>
> Andrus.
>
>
> ----- Original Message ----- >
> This is the official list of options that can be passed to the driver.
>> Unfortunately I didn't see any options for connection pooling:
>>
>> http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlo dbc/psqlodbc/docs/config-opt.html?rev=1.1&content-type=text/ plain
>>
>
>


--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Thank you.

So the program flow should be

1. Read registry key
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL Unicode\CPTimeout
2. It it does not exists or is zero, application requires registry write
access and wrote this key with value 60

Is this best solution ?
Or is there some ODBC api function which can used for this ?

I'm bit confused by the fact that pooling is not turned on by default.
So almost every PostgreSQL ODBC application does not use pooling.

Is connection pooling tested and works OK with unicode driver ?
Can pooling turned on by default in next driver release ?

Andrus.

----- Original Message -----

> I have discovered this problem a little. It seems to me installer has to
> set "CPTimeout" registry key to some integer value. This will enable
> pooling by default. Here is this registry key:
>
> HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_na me\CPTimeout


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

On Wed, Oct 15, 2008 at 8:21 AM, Andrus wrote:

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

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.

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.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

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.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

On Wed, Oct 15, 2008 at 9:58 AM, Andrus wrote:

> Why you re-invented the wheel?
This is a good point. I guess that you are right. Normally I open
one connection per application (Using a "singleton pattern") and then
pass it to any functions that need it to create or execute a
(prepared) statement. Perhaps ODBC connection pulling would eliminate
this problem, But I want to be sure that I only open one connection
per application.


> If application is idle for a some time, ADSL modems or something other in
> wire closes connection automatically.
>
> 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.

Aren't pulled connections going to suffer from the same disconnection
problems as nonpulled connections?

Maybe a better solution would be to wrap your connection in a class
that periodically checks if it is alive. If it isn't alive, then
replace the stale connection with a new connection. Also, when
wrapping your connection object you could also have it check and
optionally recreate the dead connection when an operation is requested
from it.

Of course, this won't fix the problem with long running transactions
that might fail when the connection dies, but at least you know that
your connections will always be open. However, using this approach,
you could raise a helpful error message when a connection needs to be
created while a transaction was in process.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

> Aren't pulled connections going to suffer from the same disconnection
> problems as nonpulled connections?

No.
Only idle connections seems to be killed automatically by ADSL modem.
Maybe TCP_KEEPALIVE can fix this but I havent found any inforamtion about
its usage with PostgreSQL.

Andrus.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Andrus wrote:

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

The automatic close is probably a TCP/IP connection timeout or (much
more likely) a home user's NAT router expiring connection tracking entries.

Continued activity on the connection should prevent this. See these
configuration directives, which you can set in postgresql.conf or
per-connection using the SET command:

tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 0 # TCP_KEEPCNT;

Of course, your code must still be prepared to handle broken connections
where it re-establishes the connection and retries the transaction. You
need to be prepared for transaction failures due to other transient
errors, too.

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

This won't work, because an unexpected connection break won't remove the
record.

You could use an advisory lock on some imaginary resource, but that'd be
pretty ugly.

--
Craig Ringer

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Craig,

>> Maybe TCP_KEEPALIVE can fix this but I havent found any inforamtion about
>> its usage with PostgreSQL.
>
> http://www.postgresql.org/docs/current/static/runtime-config -connection.html

Thank you for reply.

I'm sorry I was not clear. The part of
"On systems that support the*" in referenced documentation confuces me.

Windows clients use Windwses starting at Windows 98.
Servers are modern Linux, Free/NetBSD and Windows computers starting at
Windows XP.
Connections are made over public internet, clients are connecting to 5432
port in server.

Will TCP_KEEP* options work wihout issure in this configuraton?.
If I create production application which relies on TCP_KEEP* settings will
this work OK?
Can TCP_KEEP* blocked by ISP or by routers/ADSL/GPRS modems ?
I'm asking so because there was recently message in npgsql mailing list that
TCP_KEEP* are not working.
I tried google little but havent found any information about using those
options to resolve idle
connection kill issue in configuraton above.

Andrus.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

On Wed, Oct 15, 2008 at 2:47 PM, Andrus wrote:
>> Aren't pulled connections going to suffer from the same disconnection
>> problems as nonpulled connections?
>
> No.
> Only idle connections seems to be killed automatically by ADSL modem.
> Maybe TCP_KEEPALIVE can fix this but I havent found any inforamtion about
> its usage with PostgreSQL.

If your ADSL modem is also your router, and you are using NAT, then it
is not killing your connection. It is merely removing it from active
connection queue after 3 min or so of no activity.

If your ADSL modem is not a router, and "disconnects" your connection,
then the ADSL modem is broken. If you have a NAT outside the modem,
then it is the NAT that is timing out, not the modem.

TCP_KEEPALIVE messages are only useful when you have a non-public IP
and/or using NAT and/or want PostgreSQL to close connections where the
client has disappeared.

- Adam

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Andrei,

> I have discovered this problem a little. It seems to me installer has to
> set "CPTimeout" registry key to some integer value. This will enable
> pooling by default. Here is this registry key:
>
> HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_na me\CPTimeout

According to http://www.15seconds.com/issue/970531.htm
this only changes pool timeout, it does not turn pooling on.

Andrus.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Hello,

Thank you for this link. There is no special option to enable connection
pooling for the driver in the ODBC driver manager's level. Try to set
some value and you'll see that windows recognizes this feature as
enabled. I haven't actually test this, did you?

Andrus wrote:
> Andrei,
>
>> I have discovered this problem a little. It seems to me installer has
>> to set "CPTimeout" registry key to some integer value. This will
>> enable pooling by default. Here is this registry key:
>>
>> HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_na me\CPTimeout
>
> According to http://www.15seconds.com/issue/970531.htm
> this only changes pool timeout, it does not turn pooling on.
>
> Andrus.
>


--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

On Mon, Oct 20, 2008 at 1:59 PM, Andrei Kovalevski
wrote:
> Thank you for this link. There is no special option to enable connection
> pooling for the driver in the ODBC driver manager's level. Try to set some
> value and you'll see that windows recognizes this feature as enabled. I
> haven't actually test this, did you?

http://support.microsoft.com/kb/169470

--
Jonah H. Harris, Senior DBA
myYearbook.com

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Enabling connection pooling by default

Adam,

> If your ADSL modem is also your router, and you are using NAT, then it
> is not killing your connection. It is merely removing it from active
> connection queue after 3 min or so of no activity.
>
> If your ADSL modem is not a router, and "disconnects" your connection,
> then the ADSL modem is broken. If you have a NAT outside the modem,
> then it is the NAT that is timing out, not the modem.
>
> TCP_KEEPALIVE messages are only useful when you have a non-public IP
> and/or using NAT and/or want PostgreSQL to close connections where the
> client has disappeared.

Thank you.
I have the following typical configuration:

Server:

Windows computer inside LAN.
Router (Linksys, SpeedTouch etc) DHCP has programmed to assign fixed IP
address to server or fixed local IP address is assigned to server manually.
Router has public IP address assigned by ISP.
Router has programmed to forward 5432 port to server into LAN

Client:

There is similar one router in LAN which is used also to share public
internet connection.
Client router has dynamic public IP address. Client OS is Windows.

Is it possible to use this:

1. Configure something to remove 3 min NAT active connection timeout ?
Should I configure client or server ?
I havent found such option in routers.
2. Use TCP_KEELALIVEs
3. Use ping -t or some external program or website which simulates activity
4. Close and re-open connection.

Which is the best way to prevent connection loss in this case if there is no
activity for a long time ?

Andrus.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc