Re: "recovering prepared transaction" after serverrestart message

Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)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