Re: [HACKERS] DROP DATABASE always seeing database in use
"Russell Smith" <[EMAIL PROTECTED]> 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! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
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 <[EMAIL PROTECTED]> wrote:
> Jens-Wolfhard Schicke <[EMAIL PROTECTED]> 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
>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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. > > 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
Jens-Wolfhard Schicke <[EMAIL PROTECTED]> 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
Re: [HACKERS] DROP DATABASE always seeing database in use
-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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
"Heikki Linnakangas" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
Tom Lane wrote: Gregory Stark <[EMAIL PROTECTED]> writes: "Michael Fuhr" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
Gregory Stark <[EMAIL PROTECTED]> writes: > "Michael Fuhr" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
Andrew Dunstan <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
Gregory Stark wrote: "Alvaro Herrera" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
Gregory Stark wrote: > "Alvaro Herrera" <[EMAIL PROTECTED]> 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
"Alvaro Herrera" <[EMAIL PROTECTED]> 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! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
"Michael Fuhr" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP DATABASE always seeing database in use
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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-04 11: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-04 11: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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
