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 flat)
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 
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(at)iol(dot)ie                          Galway Cathedral Recitals
-------------------------------------------------------------


Responses

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-2014 The PostgreSQL Global Development Group