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

wow - I am finally back in business!!

Tom - the answer to your question is yes - these uncommitted
transactions blocked the vacuuming. As soon as i got around getting the
uncommitted transactions rollbacked - the vacuuming worked and things
seemed fine. One other little thing i had was a corrupt pg_hba.conf
file also. INteresting..... As soon as i recreated a new one -
everything seemed fine.

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?
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?

Thanks again for everybody's help!! Maria Wilson

Tom Lane wrote:
> "Maria L. Wilson" <Maria(dot)L(dot)Wilson-1(at)nasa(dot)gov> writes:
>
>> yes - how do i get rid of these??
>>
>
> Either COMMIT PREPARED or ROLLBACK PREPARED, depending on whether
> you think you want those old transactions to take effect or not.
> (Personally I'd go for ROLLBACK --- if you did not even know those
> transactions were open, you probably don't want them.)
>
> Does anyone remember whether uncommitted prepared transactions
> block vacuum cleanup in 8.2.x? These things might explain Maria's
> original problem, too.
>
> regards, tom lane
>