pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcommiting transaction from outside
5+ messages / 4 participants
[nested] [flat]
* commiting transaction from outside
@ 2011-10-03 16:50 Julius Tuskenis <julius@nsoft.lt>
2011-10-03 17:14 ` Re: commiting transaction from outside Scott Ribe <scott_ribe@elevated-dev.com>
2011-10-03 17:48 ` Re: commiting transaction from outside Julien Rouhaud <rjuju123@gmail.com>
2011-10-03 17:57 ` Re: commiting transaction from outside Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 3 replies; 5+ messages in thread
From: Julius Tuskenis @ 2011-10-03 16:50 UTC (permalink / raw)
To: pgsql-admin
Hello,
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've tried google'ing but without results - this might mean, that there
is no way, but I thought asking is an option..
Thank you in advance for any advice.
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: commiting transaction from outside
2011-10-03 16:50 commiting transaction from outside Julius Tuskenis <julius@nsoft.lt>
@ 2011-10-03 17:14 ` Scott Ribe <scott_ribe@elevated-dev.com>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Ribe @ 2011-10-03 17:14 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
On Oct 3, 2011, at 10:50 AM, Julius Tuskenis wrote:
> I've tried google'ing but without results - this might mean, that there is no way, but I thought asking is an option..
There's no way to do it, because it makes no sense. If the client app is buggy, then how do you know for sure that the client app has completed the transaction, rather than making a partial update?
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: commiting transaction from outside
2011-10-03 16:50 commiting transaction from outside Julius Tuskenis <julius@nsoft.lt>
@ 2011-10-03 17:48 ` Julien Rouhaud <rjuju123@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Julien Rouhaud @ 2011-10-03 17:48 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
Hi
I'm sorry but there's no way to commit a transaction from outside, except if
your application uses the two-phase commit (PREPARE TRANSACTION
id_transaction)
On Mon, Oct 3, 2011 at 6:50 PM, Julius Tuskenis <julius@nsoft.lt> wrote:
> Hello,
>
> 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've tried google'ing but without results - this might mean, that there is
> no way, but I thought asking is an option..
>
> Thank you in advance for any advice.
>
> --
> Julius Tuskenis
> Programavimo skyriaus vadovas
> UAB nSoft
> mob. +37068233050
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin;
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: commiting transaction from outside
2011-10-03 16:50 commiting transaction from outside Julius Tuskenis <julius@nsoft.lt>
@ 2011-10-03 17:57 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-10-03 18:17 ` Re: commiting transaction from outside Julius Tuskenis <julius@nsoft.lt>
2 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2011-10-03 17:57 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: commiting transaction from outside
2011-10-03 16:50 commiting transaction from outside Julius Tuskenis <julius@nsoft.lt>
2011-10-03 17:57 ` Re: commiting transaction from outside Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-10-03 18:17 ` Julius Tuskenis <julius@nsoft.lt>
0 siblings, 0 replies; 5+ messages in thread
From: Julius Tuskenis @ 2011-10-03 18:17 UTC (permalink / raw)
To: pgsql-admin
On 2011.10.03 20:57, Kevin Grittner wrote:
>
>
> 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
>
Thank You, Kevin, Julien, Scott for the help.
Scott - your idea is worth remembering. The problem is I can not see the
tuples until they are committed, or can I ? And if I understand you
correctly I can not rely on xmin and xmax values after the commit.
Anyway Thank You for the Idea.
I solved the problem by using sql injection methods (I was lucky the
application was buggy enough).
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-10-03 18:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-03 16:50 commiting transaction from outside Julius Tuskenis <julius@nsoft.lt>
2011-10-03 17:14 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-10-03 17:48 ` Julien Rouhaud <rjuju123@gmail.com>
2011-10-03 17:57 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-10-03 18:17 ` Julius Tuskenis <julius@nsoft.lt>
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