From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Julius Tuskenis <julius@nsoft.lt>
To: pgsql-admin@postgresql.org
Subject: Re: commiting transaction from outside
Date: Mon, 03 Oct 2011 12:57:19 -0500
Message-ID: <4E89B12F02000025000419BD@gw.wicourts.gov> (raw)
In-Reply-To: <4E89E7BF.9040009@nsoft.lt>
References: <4E89E7BF.9040009@nsoft.lt>
Julius Tuskenis <julius@nsoft.lt> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, julius@nsoft.lt
Subject: Re: commiting transaction from outside
In-Reply-To: <4E89B12F02000025000419BD@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox