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

"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.

> 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.)

regards, tom lane