Re: 2PC-induced lockup

On Tue, 2007-07-10 at 10:41 -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > The following command sequence appears to lock up the database system:
> > BEGIN;
> > LOCK pg_authid;
> > PREPARE TRANSACTION 'foo';
> > \q
>
> > After that you can't connect anymore, even in single-user mode. The
> > only way I could find is to clear out the pg_twophase directory, but
> > I'm not sure whether it is safe to do that.
>
> > Should this be prevented somehow, and is there a better recovery path?
>
> AFAICS this is just one of many ways in which a superuser can shoot
> himself in the foot; I'm not eager to try to prevent it.
>
> Right offhand, clearing pg_twophase while the system is stopped should
> be safe enough.

Safe from the perspective of the rest of the system. The prepared
transactions will clearly be lost and that might be worth millions.

I'm concerned that this advice will lead to clearing pg_twophase every
time that the system won't start properly.

I'd be much more comfortable if LOCK TABLE caused a message to the log
if it is executed on any system table. I can't really see a reason to
allow a user the ability to explicitly lock out a system table and would
prefer if that were banned completely. It's DoS if nothing else. A
simple check on LOCK TABLE won't cost much in the normal execution path.

There seems like a number of ways that unresolved prepared transactions
can cause problems. We really need to have startup mention how many
prepared transactions there are, so we have some chance of understanding
and resolving potential problems. Without such a message we might well
experience downtimes of many hours before somebody thinks to check
pg_twophase and that runs against our goal of higher availability.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com