Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A9D02B5DBE4 for ; Mon, 3 Oct 2011 14:57:30 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 74972-01 for ; Mon, 3 Oct 2011 17:57:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from gw.wicourts.gov (unknown [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 5D24CB5DBD9 for ; Mon, 3 Oct 2011 14:57:24 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 03 Oct 2011 12:57:21 -0500 Message-Id: <4E89B12F02000025000419BD@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Mon, 03 Oct 2011 12:57:19 -0500 From: "Kevin Grittner" To: "Julius Tuskenis" , Subject: Re: commiting transaction from outside References: <4E89E7BF.9040009@nsoft.lt> In-Reply-To: <4E89E7BF.9040009@nsoft.lt> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.107 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201110/8 X-Sequence-Number: 38460 Julius Tuskenis wrote: > my question my sound strange, but because of a bug in an > application I see the transaction was started and not commited > (connection state is IDLE in transaction). Is there a way to > commit this transaction? I don't want to loose data. I think if I > close the application the transaction would get rollbacked. I can't think of any way to issue the commit, unless the application is running in an unusual environment which lets you break in and issue ad hoc commands on its connection. There is a way you could fish out the effects of the transaction, although it might be a fair bit of work. Each tuple inserted or updated has the transaction ID set as its xmin in the new tuple, and every tuple deleted or updated has the transaction ID set as its xmax. The old and new are guaranteed not to go away until the transaction completes, one way or the other. With some clever programming you could capture the net effect of the transaction, and duplicate that effect after the transaction is rolled back. Be aware that while the transaction is stuck "idle in transaction" the cleanup of old tuples can't proceed normally; so if you're continuing to modify any database in the cluster, it could be accumulating bloat until you resolve this. -Kevin