Incorrect "ERROR: database "xxx" is being accessed by other users"
Hello.
I have a database which I cannot delete (the same for rename):
# psql -d template1
=# drop database xxx;
ERROR: database "xxx" is being accessed by other users
But it is NOT accessed by anybody, because:
1. ps ax|grep post shows that there is NO active connections to database
xxx;
2. I restarter postmaster and immediately try to delete the database - the
same error message
3. I stopped web-server, and now it is GUARANTEED that there is no
connections :-)
What other diagnostics could I do?
P. S.
This database has other stranges, like:
1. VALUUM FULL VERBOSE ANALYZE hands at the middle of this database and
never stops its execution;
2. there are some problems when I create a foreign key to particular table:
it also hangs (postmaster restart does not correct this)
3. database is 2-3 times slow than it could be (if I restore it from the
backup to the new place, it works 2-3 times faster)
Practically I have already dump+restore this database to a new location
(xxx_new) and work with it only, but I want to remove the old (broken?)
database xxx and I cannot...
"Dmitry Koterov" <dmitry@koterov.ru> writes:
I have a database which I cannot delete (the same for rename):
ERROR: database "xxx" is being accessed by other users
But it is NOT accessed by anybody, because:
Have you checked for prepared transactions in that DB? See
pg_prepared_xacts view (I've been burnt by that myself...)
regards, tom lane
Yes, I have one!
How to remove it now? I tried DEALLOCATE for gid returned by
select * from pg_prepared_xacts;
but it says "prepared statement does not exist"...
Database restart does not reset the prepared transaction...
Show quoted text
On 3/13/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Dmitry Koterov" <dmitry@koterov.ru> writes:
I have a database which I cannot delete (the same for rename):
ERROR: database "xxx" is being accessed by other users
But it is NOT accessed by anybody, because:Have you checked for prepared transactions in that DB? See
pg_prepared_xacts view (I've been burnt by that myself...)regards, tom lane
Dmitry Koterov wrote:
Have you checked for prepared transactions in that DB? See
pg_prepared_xacts view (I've been burnt by that myself...)Yes, I have one!
How to remove it now? I tried DEALLOCATE for gid returned byselect * from pg_prepared_xacts;
but it says "prepared statement does not exist"...
Database restart does not reset the prepared transaction...
The command is: ROLLBACK PREPARED 'yourgid'
'DEALLOCATE' is for prepared statements.
Yours,
Laurenz Albe
On Tue, Mar 13, 2007 at 11:41:46AM +0300, Dmitry Koterov wrote:
Yes, I have one!
How to remove it now? I tried DEALLOCATE for gid returned byselect * from pg_prepared_xacts;
but it says "prepared statement does not exist"...
DEALLOCATE is for prepared *statements*; you have a prepared
*transaction*. Connect to the database you're trying to drop and
use ROLLBACK PREPARED or COMMIT PREPARED, then disconnect from that
database and try dropping it again.
--
Michael Fuhr
Thanks a lot! All works!
So - I propose to change error message from
ERROR: database "xxx" is being accessed by other users
to
ERROR: database "xxx" is being accessed by other users or there are
prepared transactions exist (please use "SELECT * FROM pg_prepared_xacts"
and "ROLLBACK PREPARED ..." to fix this)
in a new PG version. Is it possible?
Show quoted text
On 3/13/07, Michael Fuhr <mike@fuhr.org> wrote:
On Tue, Mar 13, 2007 at 11:41:46AM +0300, Dmitry Koterov wrote:
Yes, I have one!
How to remove it now? I tried DEALLOCATE for gid returned byselect * from pg_prepared_xacts;
but it says "prepared statement does not exist"...
DEALLOCATE is for prepared *statements*; you have a prepared
*transaction*. Connect to the database you're trying to drop and
use ROLLBACK PREPARED or COMMIT PREPARED, then disconnect from that
database and try dropping it again.--
Michael Fuhr---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster