DROP DATABASE always seeing database in use

Gregory Stark wrote:

It seems there's something wrong with CheckOtherDBBackends() but I haven't
exactly figured out what. There are no other sessions but drop database keeps
saying "regression" is being accessed by other users. I do see Autovacuum
touching tables in regression but CheckOtherDBBackends() is supposed to send
it a sigkill if it finds it and it doesn't seem to be doing so.

I've been hacking on unrelated stuff in this database and have caused multiple
core dumps and autovacuum is finding orphaned temp tables. It's possible some
state is corrupted in some way here but I don't see what.

The buildfarm would be all red if this wasn't something local to your
installation, I think. Maybe you should get gdb on the backend and set
a breakpoint on errfinish, or maybe step into CheckOtherDBBackends to
see why it isn't working.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Mon, Aug 04, 2008 at 11:51:35AM +0100, Gregory Stark wrote:

It seems there's something wrong with CheckOtherDBBackends() but I haven't
exactly figured out what. There are no other sessions but drop database keeps
saying "regression" is being accessed by other users.

Are any prepared transactions still open?

select * from pg_prepared_xacts;

--
Michael Fuhr

"Michael Fuhr" <mike@fuhr.org> writes:

On Mon, Aug 04, 2008 at 11:51:35AM +0100, Gregory Stark wrote:

It seems there's something wrong with CheckOtherDBBackends() but I haven't
exactly figured out what. There are no other sessions but drop database keeps
saying "regression" is being accessed by other users.

Are any prepared transactions still open?

Uh, yes, I did notice that but didn't put two and two together. That does make
sense now that you mention it.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

The buildfarm would be all red if this wasn't something local to your
installation, I think. Maybe you should get gdb on the backend and set
a breakpoint on errfinish, or maybe step into CheckOtherDBBackends to
see why it isn't working.

Michael Fuhr solved it so this is academic but, the buildfarm runs make
installcheck? I thought it just ran make check

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

Gregory Stark wrote:

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

The buildfarm would be all red if this wasn't something local to your
installation, I think. Maybe you should get gdb on the backend and set
a breakpoint on errfinish, or maybe step into CheckOtherDBBackends to
see why it isn't working.

Michael Fuhr solved it so this is academic but, the buildfarm runs make
installcheck? I thought it just ran make check

Hmm, I kinda assumed that it ran "drop database regression" at some
point, but maybe you are right that it doesn't ...

I do run make installcheck all the time though, so at least I would have
noticed ;-)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Gregory Stark wrote:

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

The buildfarm would be all red if this wasn't something local to your
installation, I think. Maybe you should get gdb on the backend and set
a breakpoint on errfinish, or maybe step into CheckOtherDBBackends to
see why it isn't working.

Michael Fuhr solved it so this is academic but, the buildfarm runs make
installcheck? I thought it just ran make check

It runs both.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Gregory Stark wrote:

Michael Fuhr solved it so this is academic but, the buildfarm runs make
installcheck? I thought it just ran make check

It runs both.

It also runs contrib installcheck, which will most definitely exercise
DROP DATABASE.

regards, tom lane

Gregory Stark <stark@enterprisedb.com> writes:

"Michael Fuhr" <mike@fuhr.org> writes:

Are any prepared transactions still open?

Uh, yes, I did notice that but didn't put two and two together. That does make
sense now that you mention it.

I've been bit by that too, and so have other people. Maybe it'd be
worth the trouble to improve the message so that it explicitly tells you
when there are prepared transactions blocking the DROP.

Another possibility is to let the DROP automatically roll back the
conflicting prepared xacts, but that seems a bit dangerous.

regards, tom lane

Tom Lane wrote:

Gregory Stark <stark@enterprisedb.com> writes:

"Michael Fuhr" <mike@fuhr.org> writes:

Are any prepared transactions still open?

Uh, yes, I did notice that but didn't put two and two together. That does make
sense now that you mention it.

I've been bit by that too, and so have other people. Maybe it'd be
worth the trouble to improve the message so that it explicitly tells you
when there are prepared transactions blocking the DROP.

Yes, that should be easy enough.

Another possibility is to let the DROP automatically roll back the
conflicting prepared xacts, but that seems a bit dangerous.

Yeah, the prepared xact might have modified shared catalogs, for example.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:

Tom Lane wrote:

I've been bit by that too, and so have other people. Maybe it'd be
worth the trouble to improve the message so that it explicitly tells you
when there are prepared transactions blocking the DROP.

Yes, that should be easy enough.

I looked at this quickly and decided that we can do it with some small
changes to CheckOtherDBBackends(). I propose counting the number of
conflicting PGPROCs and adding a DETAIL line to the existing error
message:

ERROR: database "%s" is being accessed by other users
DETAIL: There are %d session(s) and %d prepared transaction(s) using the database.

I'm aware that this phrasing might not translate very nicely ... anyone
have a suggestion for better wording?

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:

ERROR: database "%s" is being accessed by other users
DETAIL: There are %d session(s) and %d prepared transaction(s) using the database.

I'm aware that this phrasing might not translate very nicely ... anyone
have a suggestion for better wording?

I can only estimate translation effort into German, but how about:

DETAIL: Active users of the database: %d session(s), %d prepared transaction(s)

Jens
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIl6G4zhchXT4RR5ARAh7BAJ4vGKx0f/1aycXOfJZmkOAg1fe2IgCgpXVe
HF9CSX3bSZI/eO4GB3xSrdc=
=Ogzl
-----END PGP SIGNATURE-----

Jens-Wolfhard Schicke <drahflow@gmx.de> writes:

Tom Lane wrote:

ERROR: database "%s" is being accessed by other users
DETAIL: There are %d session(s) and %d prepared transaction(s) using the database.

I'm aware that this phrasing might not translate very nicely ... anyone
have a suggestion for better wording?

I can only estimate translation effort into German, but how about:

DETAIL: Active users of the database: %d session(s), %d prepared transaction(s)

Hmmm ... what I ended up committing was code that special-cased the
common cases where you only have one or the other, ie

/*
* We don't worry about singular versus plural here, since the English
* rules for that don't translate very well. But we can at least avoid
* the case of zero items.
*/
if (notherbackends > 0 && npreparedxacts > 0)
errdetail("There are %d other session(s) and %d prepared transaction(s) using the database.",
notherbackends, npreparedxacts);
else if (notherbackends > 0)
errdetail("There are %d other session(s) using the database.",
notherbackends);
else
errdetail("There are %d prepared transaction(s) using the database.",
npreparedxacts);

Your proposal seems fine for the first case but a bit stilted for the
other two. Or maybe that's just me.

Of course, we don't *have* to do it as above at all, if "0 prepared
transactions" doesn't bother people.

Ideas anybody?

regards, tom lane

Gregory Stark wrote:

It seems there's something wrong with CheckOtherDBBackends() but I haven't
exactly figured out what. There are no other sessions but drop database keeps
saying "regression" is being accessed by other users. I do see Autovacuum
touching tables in regression but CheckOtherDBBackends() is supposed to send
it a sigkill if it finds it and it doesn't seem to be doing so.

I've been hacking on unrelated stuff in this database and have caused multiple
core dumps and autovacuum is finding orphaned temp tables. It's possible some
state is corrupted in some way here but I don't see what.

Autovacuum does this as well. I know on 8.1, I've been bitten by it a
number of times. I don't know for CVS or newer version than 8.1. But
it's an option worth considering as autovac doesn't show up in
pg_stat_activity.

Regards

Russell.

Maybe put the whole thing into the ERROR message instead of having a
separate DETAIL line?

ERROR: database "%s" is being accessed by %d session(s)
-or-
ERROR: database "%s'" is being accessed by %d prepared transaction(s)
-or-
ERROR: database "%s'" is being accessed by %d session(s) and %d
prepared transaction(s)

or possibly similar variants on the following, slightly more compact wording:

ERROR: database "%s'" has %d open session(s) and %d prepared transaction(s)

...Robert

"Russell Smith" <mr-russ@pws.com.au> writes:

Gregory Stark wrote:

It seems there's something wrong with CheckOtherDBBackends() but I haven't
exactly figured out what. There are no other sessions but drop database keeps
saying "regression" is being accessed by other users. I do see Autovacuum
touching tables in regression but CheckOtherDBBackends() is supposed to send
it a sigkill if it finds it and it doesn't seem to be doing so.

I've been hacking on unrelated stuff in this database and have caused multiple
core dumps and autovacuum is finding orphaned temp tables. It's possible some
state is corrupted in some way here but I don't see what.

Autovacuum does this as well. I know on 8.1, I've been bitten by it a
number of times. I don't know for CVS or newer version than 8.1. But
it's an option worth considering as autovac doesn't show up in
pg_stat_activity.

In 8.3 autovacuum politely steps out of the way if it's holding up traffic
(actually anyone who gets stuck behind vacuum just rudely shoots it in the
back). So this *shouldn't* happen any more which is why I was raising it.

However it was solved earlier by someone else. It was a a prepared
transaction. Which was precisely what my comment about "some state is
corrupted" meant. In this case the server had core dumped after preparing a
transaction and that prepared transaction was blocking the DROP DATABASE.

8.4 will now print a better message specifically pointing out the prepared
transactions for the next hapless DBA to be caught in this situation.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!