thread-index: Acb/WbOvdZwChYmSRj6jKCNwdCPB9Q==
Message-ID: <454B5B0A.7090802@enterprisedb.com>
Date: Fri, 03 Nov 2006 15:06:50 +0000
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
Content-class: urn:content-classes:message
Importance: normal
User-Agent: Thunderbird 1.5.0.7 (X11/20060927)
MIME-Version: 1.0
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Simon Riggs" <simon@2ndquadrant.com>, <joe@mcknight.de>,
	<pgsql-hackers@postgresql.org>
Subject: Re: "recovering prepared transaction" after serverrestart message
References: <e3b306b71615a24e1be5e713ad3df048@>
	<27067.1162536485@sss.pgh.pa.us>
	<1162541215.3587.507.camel@silverbirch.site>
	<1886.1162564922@sss.pgh.pa.us>
In-Reply-To: <1886.1162564922@sss.pgh.pa.us>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
>> We only care when they break, otherwise its just situation normal, yes?
> 
> No, the trouble case is where the XA manager that owns the transaction
> has forgotten about it.

Yeah, and there's no way the DBMS can detect that.

>> Is there a way to see prepared transactions where the original session
>> that prepared then has died? Perhaps the message at startup should be
>> "you have at least one prepared transaction that needs resolution".
> 
> I am completely baffled by this focus on database startup time.  That's
> not where the problem is.

Agreed. Though one way to have orphaned prepared transactions is to 
recover from a PITR backup or bring a warm stand-by live. The 
transaction manager might have committed a transaction after the backup 
was taken. Recovering from the backup resurrects the transaction again 
and the TM won't know about it.

The problem of orphaned transactions is most likely to occur on a 
dev/test environment, where the TM is run on a developer's laptop and 
might be killed and reinstalled or reconfigured at any time.

And unfortunately there's also a lot of broken TMs out there that don't 
recover from crashes properly.

I think it's a good idea to at least LOG about prepared transactions at 
startup. But it would be nice to also have a timeout, after which a big 
fat WARNING would be printed. I don't believe in killing transactions 
automatically though, that's a job for the administrator.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com