The implementation is a database firewall where postgres gets used between our corporate and production network environments on a Linux (RHEL 4.8) platform.
Were transitioning from 8.3.7 to 9.x. and were also implementing a link to postgres from SQL server 2005 using the PGNP OLE DB driver from pgoledb, we have,for the present, switched to the opensource ODBC driver because of continual issues with PGNP over SSL (they are working on it).
Our applications are base on Windows Server and Tomcat and use the Hibernate ORM with MSDTC (Microsoft Distributed Transaction Coordinator), I expect this to be able to manage transaction commits across both Postgres and SQL Server however.
1) PGNP has been leaking connections, not closing them, from SQL server
2) postgres 8.3.7 and to a lesser extent 9.0.3 has been shown to hang onto the open connections and maintain locks on the table in question.
3) For 8.3.7 the locks persist a database shutdown and startup cycle - I have had to trash and rebuild the database in question.
4) For 8.3.7 I have demonstrated that I can remedy the problem by stopping the database, deleting the contents of pg_twophase and pg_subtrans and restarting the database
My questions are:
a) Is it safe to delete the contents of pg_twophase and pg_subtrans
b) Are there any other steps I should take to mitigate unnecessary side effects of this action
c) Is there a better way?
d) Is this a bug that has been fixed and if so when, i.e. will it be Ok to deploy 9.1.x in production without support advice to follow this strategy should we get abandoned transactions in future.
Thanks
paddy carroll