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.
