Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 897E161AFBC for ; Fri, 20 Jan 2012 09:08:35 -0400 (AST) Received: from mail-we0-f174.google.com ([74.125.82.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RoECm-0002xM-P6 for pgsql-admin@postgresql.org; Fri, 20 Jan 2012 13:08:34 +0000 Received: by werb13 with SMTP id b13so383319wer.19 for ; Fri, 20 Jan 2012 05:08:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=from:content-type:subject:date:message-id:to:mime-version:x-mailer; bh=YRIVZZLr42T4rHseWo/dbfzHpQoweAcvAdVKr0NxUGs=; b=dKJF18AP/aC8X9NE8kAVt2qZy/ONS1+/HB0DtrJzh3hHElegNRdBwLVVXsp4TyM89C vId9ftsnvz1lJ0XFWwZ0VsgLtb6u+FDzSSr/HzjcsKGiQLng2JwY/tP7keoM54PHU82G 6fcR+1ibYEbq0NIgWW8U+vGGYRN/ZWj3nbXl8= Received: by 10.216.140.15 with SMTP id d15mr945288wej.16.1327064900130; Fri, 20 Jan 2012 05:08:20 -0800 (PST) Received: from [192.168.1.6] (host-78-149-132-128.as13285.net. [78.149.132.128]) by mx.google.com with ESMTPS id di5sm8932690wib.3.2012.01.20.05.08.17 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 20 Jan 2012 05:08:18 -0800 (PST) From: paddy carroll Content-Type: multipart/alternative; boundary="Apple-Mail=_0FF52845-6E70-46DA-B866-5C509E57DFCF" Subject: persistent locks Date: Fri, 20 Jan 2012 13:08:16 +0000 Message-Id: <5C0CD2F1-56F5-471D-9A5C-9C6CB041873C@gmail.com> To: pgsql-admin@postgresql.org Mime-Version: 1.0 (Apple Message framework v1251.1) X-Mailer: Apple Mail (2.1251.1) X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201201/111 X-Sequence-Number: 39023 --Apple-Mail=_0FF52845-6E70-46DA-B866-5C509E57DFCF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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). =20 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 =20 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. =20 Thanks paddy carroll= --Apple-Mail=_0FF52845-6E70-46DA-B866-5C509E57DFCF Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii 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
= --Apple-Mail=_0FF52845-6E70-46DA-B866-5C509E57DFCF--