Обсуждение: Database in use?

Periodically, my databases will give me a message that says that
someone is using the database when it appears that no one is.  What
causes this?  What can I do about it?

Thanks,
Carol

Carol Walter <walterc@indiana.edu> writes:
> Periodically, my databases will give me a message that says that
> someone is using the database when it appears that no one is.  What
> causes this?  What can I do about it?

What's the context exactly?

In some cases you can get this type of message because of the fact that
it takes finite time for an existing backend to exit.  For instance in

    psql -c "some command" somedb
    dropdb somedb

there's a nonzero probability that the backend launched for the psql
session will still exist when the dropdb runs.  Recent PG versions have
a hack in DROP DATABASE to wait a little bit to see if conflicting
backends will exit; but maybe you're running a version that hasn't got
that patch, or maybe you're doing something else.

            regards, tom lane

Hi,

 try this:
SELECT * FROM pg_stat_activity;

Maybe it will show you who is using it..


--
Lukas
UAB nSoft
http://www.nsoft.lt
Lukas at nsoft.lt
+370 655 10 655


> Periodically, my databases will give me a message that says that
> someone is using the database when it appears that no one is.  What
> causes this?  What can I do about it?
>
> Thanks,
> Carol
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>



--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

ps -ef | grep postgres


> Date: Mon, 2 Mar 2009 22:35:23 +0200
> Subject: Re: [ADMIN] Database in use?
> From: Lukas@fmf.vtu.lt
> To: pgsql-admin@postgresql.org
>
> Hi,
>
> try this:
> SELECT * FROM pg_stat_activity;
>
> Maybe it will show you who is using it..
>
>
> --
> Lukas
> UAB nSoft
> http://www.nsoft.lt
> Lukas at nsoft.lt
> +370 655 10 655
>
>
> > Periodically, my databases will give me a message that says that
> > someone is using the database when it appears that no one is. What
> > causes this? What can I do about it?
> >
> > Thanks,
> > Carol
> >
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > believed to be clean.
> >
> >
>
>
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Well, most recently I had created a new database using UTF8 encoding
and I had restored data to it, that had been in a database using
SQL_ASCII.  I wanted to rename the original database.  When I tried to
rename it, I got the message that I couldn't do it because the
database was in use.  This particular database is in a 8.2.4 release.
I'm moving to 8.3.n but I'm not there yet.

Carol

On Mar 2, 2009, at 3:32 PM, Tom Lane wrote:

> Carol Walter <walterc@indiana.edu> writes:
>> Periodically, my databases will give me a message that says that
>> someone is using the database when it appears that no one is.  What
>> causes this?  What can I do about it?
>
> What's the context exactly?
>
> In some cases you can get this type of message because of the fact
> that
> it takes finite time for an existing backend to exit.  For instance in
>
>     psql -c "some command" somedb
>     dropdb somedb
>
> there's a nonzero probability that the backend launched for the psql
> session will still exist when the dropdb runs.  Recent PG versions
> have
> a hack in DROP DATABASE to wait a little bit to see if conflicting
> backends will exit; but maybe you're running a version that hasn't got
> that patch, or maybe you're doing something else.
>
>             regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Carol Walter <walterc@indiana.edu> writes:
> Well, most recently I had created a new database using UTF8 encoding
> and I had restored data to it, that had been in a database using
> SQL_ASCII.  I wanted to rename the original database.  When I tried to
> rename it, I got the message that I couldn't do it because the
> database was in use.  This particular database is in a 8.2.4 release.
> I'm moving to 8.3.n but I'm not there yet.

OK.  Consulting the CVS history, I see that RENAME DATABASE does have
a delay in it, but only in 8.3.x.

            regards, tom lane

I'm not sure I understand.  If there is a lag time between when
someone exits the database and when the database "knows" that no one
is still in it, then that shouldn't be the problem here.  I tried a
number of time over the course of 24 hours to rename the database and
it always reported that the database was in use.  No one was logged in
but me, but the database still reported that the database was in use.
I was connect to template1 and trying to rename the other database
from there.

Carol

On Mar 2, 2009, at 4:23 PM, Tom Lane wrote:

> Carol Walter <walterc@indiana.edu> writes:
>> Well, most recently I had created a new database using UTF8 encoding
>> and I had restored data to it, that had been in a database using
>> SQL_ASCII.  I wanted to rename the original database.  When I tried
>> to
>> rename it, I got the message that I couldn't do it because the
>> database was in use.  This particular database is in a 8.2.4 release.
>> I'm moving to 8.3.n but I'm not there yet.
>
> OK.  Consulting the CVS history, I see that RENAME DATABASE does have
> a delay in it, but only in 8.3.x.
>
>             regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Carol Walter <walterc@indiana.edu> writes:
> I'm not sure I understand.  If there is a lag time between when
> someone exits the database and when the database "knows" that no one
> is still in it, then that shouldn't be the problem here.  I tried a
> number of time over the course of 24 hours to rename the database and
> it always reported that the database was in use.  No one was logged in
> but me, but the database still reported that the database was in use.

Hmph.  Are you sure nothing was connected?  One thing that frequently
trips people up here is uncommitted prepared transactions --- look in
pg_prepared_xacts.

            regards, tom lane

Hello, Carol.

how exactly are you trying to change the DB name? In console or using
some management tool like pgAdmin?


Carol Walter rašė:
> Periodically, my databases will give me a message that says that
> someone is using the database when it appears that no one is.  What
> causes this?  What can I do about it?
>
> Thanks,
> Carol
>
>


--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050

The users are accessing the database using PhpPgAdmin.  I thought that
this might happen if they closed the browser without closing logging
off.  I know that if you do that the browser will return to where you
left it, when you open it again.  I tried that with my own database,
and I couldn't reproduce the condition.

Carol

On Mar 2, 2009, at 8:31 PM, Tom Lane wrote:

> Carol Walter <walterc@indiana.edu> writes:
>> I'm not sure I understand.  If there is a lag time between when
>> someone exits the database and when the database "knows" that no one
>> is still in it, then that shouldn't be the problem here.  I tried a
>> number of time over the course of 24 hours to rename the database and
>> it always reported that the database was in use.  No one was logged
>> in
>> but me, but the database still reported that the database was in use.
>
> Hmph.  Are you sure nothing was connected?  One thing that frequently
> trips people up here is uncommitted prepared transactions --- look in
> pg_prepared_xacts.
>
>             regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

I'm using psql from the command line.  Doing an ALTER DATABASE command.

Carol

On Mar 3, 2009, at 2:36 AM, Julius Tuskenis wrote:

> Hello, Carol.
>
> how exactly are you trying to change the DB name? In console or
> using some management tool like pgAdmin?
>
>
> Carol Walter rašė:
>> Periodically, my databases will give me a message that says that
>> someone is using the database when it appears that no one is.  What
>> causes this?  What can I do about it?
>>
>> Thanks,
>> Carol
>>
>>
>
>
> --
> Julius Tuskenis
> Programavimo skyriaus vadovas
> UAB nSoft
> mob. +37068233050
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

That's interesting and is probably the answer to my question as to why
this happens.  Thanks, Nick.  What it doesn't explain is why I was
able to connect to a test database using PhpPgAdmin and close the
browser, without logging out, and I was able to rename my test
database.  I couldn't reproduce the behavior.  I wonder if it is a
property of the particular browser or desktop platform.  I was using
Firefox on a Mac.  The users use Windows.  I'm not sure which browser
they are using.

I also still want to know if there is a way to gracefully break that
persistent connection on the server side.  Using a PHP front end over
the web, I don't always know who has a browser open.

Thanks,
Carol

On Mar 4, 2009, at 9:36 AM, Hajek, Nick wrote:

>>
>> The users are accessing the database using PhpPgAdmin.  I
>> thought that this might happen if they closed the browser
>> without closing logging off.  I know that if you do that the
>> browser will return to where you left it, when you open it
>> again.  I tried that with my own database, and I couldn't
>> reproduce the condition.
>>
>> Carol
>
>
> A persistent connection is often used with PHP.  With this, PHP will
> maintain the connection after a browser is closed or a user is logged
> off so that the next time a db connection is required, the response
> time
> is better.  To break any persistent connections would require
> restarting
> the browser.
>
> Nick Hajek

This has happened or is happening to me again, only this time, it's a
database that I just created.  I restored another database into a test
database.  One of the tables is empty.  I want to drop the test
database and create a new one.  When I try to drop the test database,
I get this error -
template1=# drop database km_tezt;
ERROR:  database "km_tezt" is being accessed by other users

template1=# select * from pg_prepared_xacts;
  transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

This database is one that I created just a few hours ago just to test
some things myself.  There can be no one in it except me.  I
completely closed out of the database, closed out of the system, and
closed my terminal session.  Still I get the error.  This time the
database has not been accessed through PHP at all.

Carol

On Mar 2, 2009, at 8:31 PM, Tom Lane wrote:

> Carol Walter <walterc@indiana.edu> writes:
>> I'm not sure I understand.  If there is a lag time between when
>> someone exits the database and when the database "knows" that no one
>> is still in it, then that shouldn't be the problem here.  I tried a
>> number of time over the course of 24 hours to rename the database and
>> it always reported that the database was in use.  No one was logged
>> in
>> but me, but the database still reported that the database was in use.
>
> Hmph.  Are you sure nothing was connected?  One thing that frequently
> trips people up here is uncommitted prepared transactions --- look in
> pg_prepared_xacts.
>
>             regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Have you got any copies of psql or tools like pgadmin open. I've been
caught out by this. try

select * from pg_stat_activity

it should tell you what connections are open on the table (look at the
datname column)


Carol Walter wrote:
> This has happened or is happening to me again, only this time, it's a
> database that I just created.  I restored another database into a test
> database.  One of the tables is empty.  I want to drop the test
> database and create a new one.  When I try to drop the test database,
> I get this error -
> template1=# drop database km_tezt;
> ERROR:  database "km_tezt" is being accessed by other users
>
> template1=# select * from pg_prepared_xacts;
>  transaction | gid | prepared | owner | database
> -------------+-----+----------+-------+----------
> (0 rows)
>
> This database is one that I created just a few hours ago just to test
> some things myself.  There can be no one in it except me.  I
> completely closed out of the database, closed out of the system, and
> closed my terminal session.  Still I get the error.  This time the
> database has not been accessed through PHP at all.
>
> Carol
>
> On Mar 2, 2009, at 8:31 PM, Tom Lane wrote:
>
>> Carol Walter <walterc@indiana.edu> writes:
>>> I'm not sure I understand.  If there is a lag time between when
>>> someone exits the database and when the database "knows" that no one
>>> is still in it, then that shouldn't be the problem here.  I tried a
>>> number of time over the course of 24 hours to rename the database and
>>> it always reported that the database was in use.  No one was logged in
>>> but me, but the database still reported that the database was in use.
>>
>> Hmph.  Are you sure nothing was connected?  One thing that frequently
>> trips people up here is uncommitted prepared transactions --- look in
>> pg_prepared_xacts.
>>
>>             regards, tom lane
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>
>

Carol Walter <walterc@indiana.edu> writes:
> This has happened or is happening to me again, only this time, it's a
> database that I just created.  I restored another database into a test
> database.  One of the tables is empty.  I want to drop the test
> database and create a new one.  When I try to drop the test database,
> I get this error -
> template1=# drop database km_tezt;
> ERROR:  database "km_tezt" is being accessed by other users

> template1=# select * from pg_prepared_xacts;
>   transaction | gid | prepared | owner | database
> -------------+-----+----------+-------+----------
> (0 rows)

Nothing in pg_stat_activity either?

            regards, tom lane

On Wed, Mar 4, 2009 at 2:40 PM, Carol Walter <walterc@indiana.edu> wrote:
> This has happened or is happening to me again, only this time, it's a
> database that I just created.  I restored another database into a test
> database.  One of the tables is empty.  I want to drop the test database and
> create a new one.  When I try to drop the test database, I get this error -
> template1=# drop database km_tezt;
> ERROR:  database "km_tezt" is being accessed by other users
>
> template1=# select * from pg_prepared_xacts;
>  transaction | gid | prepared | owner | database
> -------------+-----+----------+-------+----------
> (0 rows)

What does "select * from pg_stat_activity say about this db?

> This database is one that I created just a few hours ago just to test some
> things myself.  There can be no one in it except me.  I completely closed

Correct me if I'm wrong, but isn't that enough  ((just you) to cause
the drop database to fail?

I had forgotten that I had used Aqua Data Studio to draw ERD's for
this test database.  Even though, I closed it, it still had the
database open.  When I reopened it, and did a disconnect, if allowed
me to drop the database.

Thanks,

Carol

On Mar 4, 2009, at 4:45 PM, John Lister wrote:

> Have you got any copies of psql or tools like pgadmin open. I've
> been caught out by this. try
>
> select * from pg_stat_activity
>
> it should tell you what connections are open on the table (look at
> the datname column)
>
>
> Carol Walter wrote:
>> This has happened or is happening to me again, only this time, it's
>> a database that I just created.  I restored another database into a
>> test database.  One of the tables is empty.  I want to drop the
>> test database and create a new one.  When I try to drop the test
>> database, I get this error -
>> template1=# drop database km_tezt;
>> ERROR:  database "km_tezt" is being accessed by other users
>>
>> template1=# select * from pg_prepared_xacts;
>> transaction | gid | prepared | owner | database
>> -------------+-----+----------+-------+----------
>> (0 rows)
>>
>> This database is one that I created just a few hours ago just to
>> test some things myself.  There can be no one in it except me.  I
>> completely closed out of the database, closed out of the system,
>> and closed my terminal session.  Still I get the error.  This time
>> the database has not been accessed through PHP at all.
>>
>> Carol
>>
>> On Mar 2, 2009, at 8:31 PM, Tom Lane wrote:
>>
>>> Carol Walter <walterc@indiana.edu> writes:
>>>> I'm not sure I understand.  If there is a lag time between when
>>>> someone exits the database and when the database "knows" that no
>>>> one
>>>> is still in it, then that shouldn't be the problem here.  I tried a
>>>> number of time over the course of 24 hours to rename the database
>>>> and
>>>> it always reported that the database was in use.  No one was
>>>> logged in
>>>> but me, but the database still reported that the database was in
>>>> use.
>>>
>>> Hmph.  Are you sure nothing was connected?  One thing that
>>> frequently
>>> trips people up here is uncommitted prepared transactions --- look
>>> in
>>> pg_prepared_xacts.
>>>
>>>            regards, tom lane
>>>
>>> --
>>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>>

On Mar 4, 2009, at 4:53 PM, Scott Marlowe wrote:

> On Wed, Mar 4, 2009 at 2:40 PM, Carol Walter <walterc@indiana.edu>
> wrote:
>> This has happened or is happening to me again, only this time, it's a
>> database that I just created.  I restored another database into a
>> test
>> database.  One of the tables is empty.  I want to drop the test
>> database and
>> create a new one.  When I try to drop the test database, I get this
>> error -
>> template1=# drop database km_tezt;
>> ERROR:  database "km_tezt" is being accessed by other users
>>
>> template1=# select * from pg_prepared_xacts;
>>  transaction | gid | prepared | owner | database
>> -------------+-----+----------+-------+----------
>> (0 rows)
>
> What does "select * from pg_stat_activity say about this db?
>
>> This database is one that I created just a few hours ago just to
>> test some
>> things myself.  There can be no one in it except me.  I completely
>> closed
>
> Correct me if I'm wrong, but isn't that enough  ((just you) to cause
> the drop database to fail?
>
Yes, of course, "just me" is enough for it to fail.  I thought that I
wasn't in it, either. That's what I meant.  As it turned out, I was in
it, having used Aqua Data Studio to draw an ERD.  Everything worked
after I got back into Aqua Data and disconnected from the database.  I
was able to drop the database.

I still have my original question though.  Is there a graceful way to
close all connections to a database from the server side.  I'm in an
academic environment and I can count on people not writing bad code.
I don't want it to close down in the midst of someone's query, but I
would like to be able to disconnect users if the aren't running
queries.  We have regular system maintenance on Friday evenings.
Currently, the only way I have to break these connections is to stop
the database.  That stops it for all databases, when only one may be
the problem.  I would like to be able to do this when there is a
problem with a database and not have to wait until my scheduled
maintenance.
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Wed, Mar 4, 2009 at 2:40 PM, Carol Walter <walterc@indiana.edu> wrote:
>> This database is one that I created just a few hours ago just to test some
>> things myself. �There can be no one in it except me.

> Correct me if I'm wrong, but isn't that enough  ((just you) to cause
> the drop database to fail?

If you try to drop the current session's database, you get a different
error message.

regression=# drop database regression;
ERROR:  cannot drop the currently open database

            regards, tom lane