From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Maria(dot)L(dot)Wilson-1(at)nasa(dot)gov |
Cc: | "Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS APPLICATIONS]" <m(dot)l(dot)wilson(at)nasa(dot)gov>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: postgres 8.2.9 can't drop database in single user mode |
Date: | 2009-04-22 17:31:00 |
Message-ID: | 6696.1240421460@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-04-22 18:18:49 | Re: |
Previous Message | Joshua D. Drake | 2009-04-22 17:13:58 | Re: postgres 8.2.9 can't drop database in single user mode |