Thread: DROP DATABASE always seeing database in use
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.
postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query
| waiting | xact_start | query_start | backend_start |
client_addr| client_port
-------+----------+---------+----------+---------+---------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------11505
|postgres | 5616 | 10 | stark | select * from pg_stat_activity; | f | 2008-08-04 11:46:05.438479+01 |
2008-08-0411:46:05.438956+01 | 2008-08-04 11:45:19.827702+01 | | -1
(1 row)
postgres=# commit;
COMMIT
postgres=# drop database regression;
ERROR: 55006: database "regression" is being accessed by other users
LOCATION: dropdb, dbcommands.c:678
select * from pg_stat_activity;
postgres=# datid | datname | procpid | usesysid | usename | current_query | waiting |
xact_start | query_start | backend_start | client_addr | client_port
-------+----------+---------+----------+---------+---------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------11505
|postgres | 5616 | 10 | stark | select * from pg_stat_activity; | f | 2008-08-04 11:46:45.619642+01 |
2008-08-0411:46:45.620115+01 | 2008-08-04 11:45:19.827702+01 | | -1
(1 row)
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning
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
On Tue, Aug 5, 2008 at 1:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
"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'sPostgreSQL training!