Thread: 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...

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 by
>
> select * 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 by
>
> select * 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?