Re: [HACKERS] "recovering prepared transaction" after serverrestart message
Tom Lane wrote: "Simon Riggs" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] "recovering prepared transaction" after serverrestart message
"Simon Riggs" <[EMAIL PROTECTED]> 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. > 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. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] "recovering prepared transaction" after serverrestart message
On Fri, 2006-11-03 at 01:48 -0500, Tom Lane wrote: > I agree that there's a usability issue here though; I've been burnt by > forgotten prepared xacts myself (eg by control-C'ing pg_regress at just > the wrong time). Would it help if we included prepared xacts in the > pg_stat_activity view? > Is there any other place we could make them > more visible during normal server operation? We only care when they break, otherwise its just situation normal, yes? 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". We need something at that point, otherwise a PITR recovery is fairly likely to contain them and we need to know that. Otherwise on a system using prepared transactions heavily you may not spot the odd or two that have crashed and need resolution. Presumably they will effect oldestxmin, so its fairly important to be able to resolve them in a timely manner or at least know they are there. Not that I am advising their use though -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
