On Tue, 2007-07-10 at 10:41 -0400, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
