Persistant Idle Connections

Report PGNP bugs.

Persistant Idle Connections

Postby sray » Tue Aug 14, 2012 5:46 am

We seem to be getting a lot of stale idle connections on our Postgres server. All these connections are coming from our SQL Server and the pgoledb driver and appear to happen randomly over time as scheduled jobs run.

This appears to be happening with different scheduled tasks but there is one daily task that appears to trigger the scenario every time it runs. This particular SQL Server Stored Procedure uses the following syntax:

SET XACT_ABORT ON
BEGIN TRAN

SELECT ... FROM OPENQUERY(BBI, 'SELECT .. FROM xxxx')
SELECT ... FROM OPENQUERY(BBI, 'SELECT .. FROM xxxx')
INSERT INTO [bbi].[db].[schema].[table] ...
DELETE FROM [bbi].[db].[schema].[table]
INSERT INTO [bbi].[db].[schema].[table] ...
EXEC('UPDATE table SET ... WHERE ...') AT BBI

COMMIT TRAN

This created 2 new Postgres connections, both of which were left as idle connections and one of which looks to be idle in a transaction as it has an xact_start timestamp (but has no table locks).

Looking at the PGNP Profiler log I see that it starts 10 Transactions but only lists 9 Commits. I can provide the full log output if required.

PGNP Version: 1.3.0.2265
SQL Server : 2008 SP2 (10.0.5500.0) x64
SQL OS: Windows 2008 (NT 6.0 6002)
Postgres Version: 8.4.2
Postgres OS: Ubuntu 8

The Linked Server (BBI) is configured as:
* Collation Compatible : False
* Data Access : True
* RPC : True
* RPC Out : True
* Use Remote Collation : True
* Collation Name :
* Connection TImeout : 300
* Query Timeout : 300
* Distributor : False
* Publisher : False
* Subscriber : False
* Lazy Schema Validation : False
* Enable Promotion of Distributed Transactions : True

PGNP is configured as:
* Dynamic Parameter : Enabled
* Nested Queries : Enabled
* Level Zero Only : Disabled
* Allow Inprocess : Enabled
* Non Transacted Updates : Disabled
* Index as Access Path : Disabled
* Disallow Adhoc Access : Disabled
* Supports 'Like' Operator : Enabled

Any help would be most appreciated!

Seoras.
sray
 
Posts: 5
Joined: Tue Feb 28, 2012 10:59 am

Re: Persistant Idle Connections

Postby Moderator » Thu Aug 16, 2012 9:58 pm

Thank you for reporting this issue! We are able reproduce it, and we are working on a fix. We will send update as soon as the fix is ready.
User avatar
Moderator
Site Admin
 
Posts: 305
Joined: Wed Oct 29, 2008 11:27 pm

Re: Persistant Idle Connections

Postby alex_b2 » Wed Nov 14, 2012 1:16 am

Please tell me, when will this fix?
alex_b2
 
Posts: 1
Joined: Wed Nov 14, 2012 1:12 am


Return to Report Bugs

Who is online

Users browsing this forum: No registered users and 0 guests