Persistant Idle Connections

More
6 years 8 months ago #6649 by sray
sray created the topic: Persistant Idle Connections
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]. ...
DELETE FROM [bbi].[db].[schema].
INSERT INTO [bbi].[db].[schema].
...
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.

Please Log in or Create an account to join the conversation.

More
6 years 8 months ago #6652 by Moderator
Moderator replied the topic: Re: Persistant Idle Connections
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.

Please Log in or Create an account to join the conversation.

More
6 years 5 months ago #9816 by alex_b2
alex_b2 replied the topic: Re: Persistant Idle Connections
Please tell me, when will this fix?

Please Log in or Create an account to join the conversation.

Time to create page: 0.114 seconds
Powered by Kunena Forum