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

postgres 8.2.9 can't drop database in single user mode

From
"Maria L. Wilson"
Date:
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>


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

From
"Maria L. Wilson"
Date:
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>
>
>
>

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

From
Tom Lane
Date:
"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

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

From
"Maria L. Wilson"
Date:
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
>

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

From
Tom Lane
Date:
"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

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

From
Alvaro Herrera
Date:
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.

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

From
"Maria L. Wilson"
Date:
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
>

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

From
"Joshua D. Drake"
Date:
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


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

From
Tom Lane
Date:
"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

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

From
Simon Riggs
Date:
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


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

From
"Greg Sabino Mullane"
Date:
-----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-----