Re: postgres 8.2.9 can't drop database in single user mode

On Wed, 2009-04-22 at 13:31 -0400, Tom Lane wrote:
> "Maria L. Wilson" <Maria(dot)L(dot)Wilson-1(at)nasa(dot)gov> 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