psqlODBC with Visual Studio 2005 and Connection Pooling for newbies - Mailing list pgsql-odbc

Hi guys,

I started using psqlODBC with Visual Studio 2005 a few weeks ago to
build a C# app. I've done a non-trivial amount of db stuff before but
not much PostgreSQL and nothing from Visual Studio. I thought I should
share my experience with you guys, because I got really stuck on a
simple problem that almost caused me to scrap psqlODBC altogether.

Once I got all the basics under control and started populating my
database I found my program frustratingly slow, even though I was
running the postgresql server on localhost. I'm using the new
TableAdapter/Dataset stuff that Microsoft introduced with VS 2005, where
it does a lot of the connection management for you and gives you
strongly typed queries etc.. Anyway, I enabled commlog and saw that my
program was disconnecting and reconnecting for every single query,
meaning that 2000 "delete * from mytable" took over 10 mins. I'm sure
this is obvious to you guys but it took me quite a while to figure out
that my program wasn't using Connection Pooling, and longer still to
work out that I had to go to the Data Sources (ODBC) control panel in
Administrative Tools to enable Connection Pooling for the PostgreSQL
ANSI/Unicode drivers. After I did that my 2000 queries took about 10
seconds (massive speedup), and I could see in the commlog that my
program was executing the queries immediately after each other without
disconnecting. Still 2~3 times slower than executing the queries
directly with npgsql (I assume because of the overhead created by the
TableAdapter stuff) but good enough for my program.

Anyway, as far as I can tell the driver is installed by default with
Connection Pooling off which means that most newbies like me are going
to hit the performance problem and think that something is wrong with
psqlODBC. I thought it might be worth putting a note in the README
alerting people to this.

At this stage I'm thinking of having my app forcibly turn Connection
Pooling on by setting the Windows Registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL Unicode
CPTimeout = 60

Is this the way to go? Or is there a way that I can enable it for my
program only with the Connection String? The Visual Studio help files
seemed to indicate that for ODBC you can only set it on a system-wide basis.

Anyway, thanks for the great driver guys. Keep up the good work!

Patrick

P.S. One other note while I'm writing, in
FAQ 3.3) What do I need to do to establish a connection to a database?
the second-last item in the checklist says:
Postmaster must run with the -i option , or tcpip=true in
postgresql.conf to allow remote connections.

This should be updated to the statement in the PostgreSQL 8.1.0
Documentation:
Add tcpip = true to the postgresql.conf file for Versions 7.3.x and
7.4.x, or listen_addresses='*' for Version 8.0.x and above