On Wed, 2009-04-22 at 13:31 -0400, Tom Lane wrote: > "Maria L. Wilson" <Maria.L.Wilson-1@xxxxxxxx> writes: > > Question - is there any maintenance type item that we could to to check > > for uncommitted transactions on a regular basis - outside of the > > pg_prepared_xacts table? > > pg_prepared_xacts is the only SQL-level visibility there is. From a > monitoring standpoint it might be easier to watch for files in the > $PGDATA/pg_twophase/ directory, but that's just a different view of > the same information. We discussed having startup mention there were outstanding two-phase xacts, though it was blocked for some reason. > > How about from a developers position - most of our code accessing > > the databases is jboss/java/jdbc. What could have happened from the > > code side that caused these uncommitted transactions? > > Basically, somebody issued PREPARE TRANSACTION and then walked away > without either committing or rolling back. As a rule it's a bad idea > to use PREPARE TRANSACTION unless you've bought into the whole XA > concept including an external "transaction monitor" that keeps track > of open two-phase transactions across a set of related databases. > > If you don't think that there is anything like that that this DB should > be involved in, you might want to set max_prepared_transactions = 0 > to prevent future mistaken issuances of PREPARE TRANSACTION. > (Bear in mind that you have to restart Postgres to make such a change > take effect.) I think we should include further measures on this: * A command to rollback or commit all prepared transactions: COMMIT PREPARED ALL or ROLLBACK PREPARED ALL (or a function to do this). * If you issue normal COMMIT or ROLLBACK immediately after a PREPARE it says "there is no transaction in progress". It should issue a more sensible warning such as "you used the wrong command". * Same idea, taken further: If you issue anything other than a COMMIT PREPARED or ROLLBACK PREPARED on a session *after* issuing a PREPARE TRANSACTION then it should give an ERROR. If the session continues to exist then the server has not crashed and so it must always be programming error. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
