Skip site navigation (1) Skip section navigation (2)

Connections sitting idle...

From: "Raymond O'Donnell" <rod(at)iol(dot)ie>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Connections sitting idle...
Date: 2002-12-05 00:31:18
Message-ID: 3DEE9E56.22073.8B2CE8@localhost (view raw or whole thread)
Lists: 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 

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 

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>"
set rs = nothing

set conn = nothing


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

DSN info: 
conn = 69075064, PGAPI_Connect(DSN='gost', UID='********', 
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, 
                text_as_longvarchar=1, unknowns_as_longvarchar=0, 
bools_as_char=0 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings='' 
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
rod(at)iol(dot)ie                          Galway Cathedral Recitals


pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2002-12-05 02:53:09
Subject: Re: Connections sitting idle...
Previous:From: Tom LaneDate: 2002-12-04 16:19:38
Subject: Re: Drop column and Access

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group