Thread: force drop of database others are accessing

Is there any way to forcibly disconnect all users from a
database, or else force the drop of the database even
though others are still connected?

I have a report I run where I first drop the old report db
then create it anew, read in a recent dump file and then
run a bunch of queries to build up the reports.

Every once in a while my automated script fails because
some other system which should have disconnected
failed to unhook. The old data and old connection are
not important to me. The new run needs to go through.

It seems like maybe using schema I could do this ... since
that does not remove the database, and I could drop ...
cascade to make sure everything goes, but all of my scripts
assume separate databases now, and they need to be
rethought and rewritten to work with schema.

Any ideas?

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail

On Sat, Jan 24, 2004 at 12:18:06AM +0000, Lee Harr wrote:
> Every once in a while my automated script fails because
> some other system which should have disconnected
> failed to unhook. The old data and old connection are
> not important to me. The new run needs to go through.

You can look for people connected and kick them out with kill -2 on
their pid.  The trick is to start your own script (which probably
isn't "failing" exactly, but blocking, right?), and then (since
you've locked the relevant objects that way, such that nobody else
can get in) use the shell to kill -2 other connections.  Evil and
possibly dangerous, but you said you didn't care about the old data.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to.  That actually seems sort of quaint now.
        --J.D. Baldwin

>>Every once in a while my automated script fails because
>>some other system which should have disconnected
>>failed to unhook. The old data and old connection are
>>not important to me. The new run needs to go through.
>
>You can look for people connected and kick them out with kill -2 on
>their pid.  The trick is to start your own script (which probably
>isn't "failing" exactly, but blocking, right?), and then (since
>you've locked the relevant objects that way, such that nobody else
>can get in) use the shell to kill -2 other connections.  Evil and
>possibly dangerous, but you said you didn't care about the old data.
>


Yea, grepping through the output from ps and killing the other
backend process was my first thought.  The only problem is
that the user that owns the database (and runs the automatic
report script) is not necessarily the one that is causing the
problem. So that means somehow I need to kick off another
script with elevated priveleges to take care of it ...

I would rather not have to do that, but I do not see another way.

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail

On Sat, 24 Jan 2004, Lee Harr wrote:

> >>Every once in a while my automated script fails because
> >>some other system which should have disconnected
> >>failed to unhook. The old data and old connection are
> >>not important to me. The new run needs to go through.
> >
> >You can look for people connected and kick them out with kill -2 on
> >their pid.  The trick is to start your own script (which probably
> >isn't "failing" exactly, but blocking, right?), and then (since
> >you've locked the relevant objects that way, such that nobody else
> >can get in) use the shell to kill -2 other connections.  Evil and
> >possibly dangerous, but you said you didn't care about the old data.
> >
>
>
> Yea, grepping through the output from ps and killing the other
> backend process was my first thought.  The only problem is
> that the user that owns the database (and runs the automatic
> report script) is not necessarily the one that is causing the
> problem. So that means somehow I need to kick off another
> script with elevated priveleges to take care of it ...
>
> I would rather not have to do that, but I do not see another way.

If you are creating a database from scratch why not create a brand new
database and drop the old one at some later point in time when no one is
connected.  This has the additional benefit of the old db being available
while the new one is being built.

Kris Jurka

>>So that means somehow I need to kick off another
>>script with elevated priveleges to take care of it ...
>>
>>I would rather not have to do that, but I do not see another way.
>
>If you are creating a database from scratch why not create a brand new
>database and drop the old one at some later point in time when no one is
>connected.  This has the additional benefit of the old db being available
>while the new one is being built.


That is a great idea.

No one is around to access the old one in the middle of the night,
but at least the new db will be built and I can just rename it next
morning if the old one had any connections blocking the process.

Thank you!

_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail