Re: pg_twophase and pg_subtrans

Wrong list. This is a pgAdmin discussion list (graphical Postgres front-end
client).
You should post your questions in one of the following PostgreSQL related
lists, probably pgsql-admin or pgsql-bugs:

http://www.postgresql.org/community/lists/

Regards.

On Fri, Jan 20, 2012 at 04:10, paddy carroll <paddy(dot)carroll(at)gmail(dot)com>wrote:

> 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
>