pg_twophase and pg_subtrans

From: paddy carroll <paddy(dot)carroll(at)gmail(dot)com>
To: pgadmin-support(at)postgresql(dot)org
Subject: pg_twophase and pg_subtrans
Date: 2012-01-20 07:10:56
Message-ID: 33B41C86-F8A3-4E5F-9749-BAC5B800E77B@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

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

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Fernando Hevia 2012-01-20 16:49:03 Re: pg_twophase and pg_subtrans
Previous Message Raymond O'Donnell 2012-01-19 19:42:12 Re: Connecting to postgresql with pdadmin III - sucess !!!