Connections sitting idle... - Mailing list pgsql-odbc

Hello all,

I've been having a lot of trouble with connections sitting around
idle for a long time after a query has executed. I'm building a web
app which uses ADO on a windows computer to talk to the ODBC driver
(version 7.02.0004, installed with pgAdmin II), which then talks to
PostgreSQL on a Linux machine.

I've been looking at the psqlodbc-xxxxx.log and basically, what is
happening is that the query is executed as normal, then nothing
happens for about 80 seconds (during which I can see the idle backend

process on the linux server), and then suddenly the command
"PGAPI_Disconnect" appears in the log and the backend finally
terminates.

I've tried this (i) calling the ADO objects from an ASP script (see
[1] below) and (ii) calling the ADO objects from a COM DLL created in

Delphi (pretty much the same as in the ASP script) to be called from
an ASP script. In both cases, the backend hung around for about 80s,
even though the connection has been explicitly closed and the
connection object removed from memory. I've appended a typical set of

log entries at [2] below.

Interestingly, creating another Delphi app that talks to Postgres via

either the Zeos components (which don't use either ADO or ODBC)
causes the backed to die immediately once the connection is closed.
This seems to imply to me that either ADO or the ODBC driver itself
is for some reason keeping the connection open rather than closing it

immediately. This is causing me major grief, as when a lot of people
hit the web app together Postgres quickly runs out of available
connections and the server has to be rebooted.

I'm using PostgreSQL 7.2 on the production machine, 7.1 on my own
development machine, version 7.02.0004 of the ODBC driver, and ADO
2.5 (at least) and Windows 2000 Server with IIS5 on the web server.

Any help would be greatly appreciated.........before I lose all my
hair.............

Many thanks in advance.

--Ray O'Donnell

------------------------------
[1] The ASP script

<%@ language="vbscript" %>
<% option explicit %>

<!-- #include file="adovbs.asp" -->
<!-- #include file="connectionstuff.asp" -->

<%
dim strSQL
dim conn
dim rs

strSQL = "select uid from surveyowners where uid='rod'"

set conn = Server.CreateObject("ADODB.Connection")
conn.Open ConnectionStr

set rs = conn.Execute(strSQL, , adCmdText)
Response.Write rs("uid") & "<br>"
rs.Close
set rs = nothing

conn.Close
set conn = nothing

Response.Write("Done!")
Response.End
%>

------------------------------
[2] A typical pgsqlodbc-*****.log


DSN info:
DSN='gost',server='********',port='5432',dbase='gost',user='********',
passwd='********'

onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable=
'0'
          conn_settings='',conn_encoding='OTHER'
          translation_dll='',translation_option=''
conn = 69075064, PGAPI_Connect(DSN='gost', UID='********',
PWD='********')
Global Options: Version='07.02.0004', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=69075064, query=' '
conn=69075064, query='select version()'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.1.3 on i486-pc-linux-
gnu, compiled by GCC egcs-2.91.66' ]
    [ PostgreSQL version number = '7.1' ]
conn=69075064, query='set DateStyle to 'ISO''
conn=69075064, query='set geqo to 'OFF''
conn=69075064, query='select oid from pg_type where typname='lo''
    [ fetched 0 rows ]
conn=69075064, query='Show Client_Encoding'
NOTICE from backend during send_query: 'NOTICE:  Current client
encoding is SQL_ASCII
'
    [ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=69075064, query='select * from surveyowners
'
    [ fetched 1 rows ]
conn=69075064, PGAPI_Disconnect

------------------------------


-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@iol.ie                          Galway Cathedral Recitals
-------------------------------------------------------------