Thread: postgres 8.2.9 can't drop database in single user mode
i'm getting the following error..... any ideas why this would be in single user mode?? backend> drop database "scoogan_userprofile_1.2" 2009-04-21 18:18:23.032 EDT [11767] [] WARNING: database "ange" must be vacuumed within 976081 transactions 2009-04-21 18:18:23.032 EDT [11767] [] HINT: To avoid a database shutdown, execute a full-database VACUUM in "ange". 2009-04-21 18:18:23.053 EDT [11767] [] ERROR: database "scoogan_userprofile_1.2" is being accessed by other users 2009-04-21 18:18:23.053 EDT [11767] [] STATEMENT: drop database "scoogan_userprofile_1.2" backend>
this is the command that i used to start the backend.... postgres --single -D /data/pg_devices ange Maria L. Wilson wrote: > i'm getting the following error..... any ideas why this would be in > single user mode?? > > backend> drop database "scoogan_userprofile_1.2" > 2009-04-21 18:18:23.032 EDT [11767] [] WARNING: database "ange" must > be vacuumed within 976081 transactions > 2009-04-21 18:18:23.032 EDT [11767] [] HINT: To avoid a database > shutdown, execute a full-database VACUUM in "ange". > 2009-04-21 18:18:23.053 EDT [11767] [] ERROR: database > "scoogan_userprofile_1.2" is being accessed by other users > 2009-04-21 18:18:23.053 EDT [11767] [] STATEMENT: drop database > "scoogan_userprofile_1.2" > > backend> > > >
"Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov> writes:
> i'm getting the following error..... any ideas why this would be in
> single user mode??
Maybe you've got some prepared transactions laying about?
Check pg_prepared_xacts ...
regards, tom lane
yes - how do i get rid of these??
backend> select * from pg_prepared_xacts;
2009-04-22 07:43:27.421 EDT [11767] [] WARNING: database "ange" must
be vacuumed within 976080 transactions
2009-04-22 07:43:27.421 EDT [11767] [] HINT: To avoid a database
shutdown, execute a full-database VACUUM in "ange".
1: transaction (typeid = 28, len = 4, typmod = -1, byval = t)
2: gid (typeid = 25, len = -1, typmod = -1, byval = f)
3: prepared (typeid = 1184, len = 8, typmod = -1, byval = f)
4: owner (typeid = 19, len = 64, typmod = -1, byval = f)
5: database (typeid = 19, len = 64, typmod = -1, byval = f)
----
1: transaction = "339484494" (typeid = 28, len = 4, typmod = -1,
byval = t)
2: gid = "257_cGMwNzQvNjAyNA==_MQ==" (typeid = 25, len = -1,
typmod = -1, byval = f)
3: prepared = "2008-07-09 19:34:32.803537-04" (typeid = 1184,
len = 8, typmod = -1, byval = f)
4: owner = "webaskin" (typeid = 19, len = 64, typmod = -1, byval
= f)
5: database = "walt_ange" (typeid = 19, len = 64, typmod = -1,
byval = f)
----
1: transaction = "1017044215" (typeid = 28, len = 4, typmod =
-1, byval = t)
2: gid = "257_bHJjMjAwNjExNjkyLzUwNTg=_MQ==" (typeid = 25, len =
-1, typmod = -1, byval = f)
3: prepared = "2008-11-12 17:30:52.363215-05" (typeid = 1184,
len = 8, typmod = -1, byval = f)
4: owner = "scoogan" (typeid = 19, len = 64, typmod = -1, byval = f)
5: database = "scoogan_userprofile_1.2" (typeid = 19, len = 64,
typmod = -1, byval = f)
----
1: transaction = "1543806025" (typeid = 28, len = 4, typmod =
-1, byval = t)
2: gid = "257_bHJjMjAwNjExNjkxLzIwOTQ=_Mg==" (typeid = 25, len =
-1, typmod = -1, byval = f)
3: prepared = "2009-02-18 13:51:43.485558-05" (typeid = 1184,
len = 8, typmod = -1, byval = f)
4: owner = "bruno" (typeid = 19, len = 64, typmod = -1, byval = f)
5: database = "bruno_userProfile_1_2" (typeid = 19, len = 64,
typmod = -1, byval = f)
----
backend>
Tom Lane wrote:
> "Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov> writes:
>
>> i'm getting the following error..... any ideas why this would be in
>> single user mode??
>>
>
> Maybe you've got some prepared transactions laying about?
> Check pg_prepared_xacts ...
>
> regards, tom lane
>
"Maria L. Wilson" <Maria.L.Wilson-1@nasa.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
Maria L. Wilson wrote: > yes - how do i get rid of these?? > > backend> select * from pg_prepared_xacts; > 2009-04-22 07:43:27.421 EDT [11767] [] WARNING: database "ange" must > be vacuumed within 976080 transactions > 2009-04-22 07:43:27.421 EDT [11767] [] HINT: To avoid a database > shutdown, execute a full-database VACUUM in "ange". > 1: transaction (typeid = 28, len = 4, typmod = -1, byval = t) > 2: gid (typeid = 25, len = -1, typmod = -1, byval = f) > 3: prepared (typeid = 1184, len = 8, typmod = -1, byval = f) > 4: owner (typeid = 19, len = 64, typmod = -1, byval = f) > 5: database (typeid = 19, len = 64, typmod = -1, byval = f) > ---- > 1: transaction = "339484494" (typeid = 28, len = 4, typmod = -1, > byval = t) > 2: gid = "257_cGMwNzQvNjAyNA==_MQ==" (typeid = 25, len = -1, ROLLBACK PREPARED '257_cGMwNzQvNjAyNA==_MQ=='; and so on. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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.L.Wilson-1@nasa.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
>
On Wed, 2009-04-22 at 13:10 -0400, Maria L. Wilson wrote: > wow - I am finally back in business!! > 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? The use of XA/two phase commit transactions that assume rollback on disconnect. Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
"Maria L. Wilson" <Maria.L.Wilson-1@nasa.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
On Wed, 2009-04-22 at 13:31 -0400, Tom Lane wrote: > "Maria L. Wilson" <Maria.L.Wilson-1@nasa.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
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 "Maria L. Wilson" asked: > Question - is there any maintenance type item that we could > check for uncommitted transactions on a regular basis - outside > of the pg_prepared_xacts table? The latest version of check_postgres (a Postgres monitoring script) has a check for this exact problem: http://bucardo.org/check_postgres/ Details on this particular check: http://bucardo.org/check_postgres/check_postgres.pl.html#prepared_txns - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200905051117 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkoAWV4ACgkQvJuQZxSWSsjh+wCfcMjLHf0lj9ZfrvzN61XVx2Ui ECsAn0yjrH6/euQHGboVmzId/xocpHo7 =sCuO -----END PGP SIGNATURE-----