Skip site navigation (1) Skip section navigation (2)

cron job failing with 'database "xyz" is being accessed by other users' errors

From: tlm <tlm(dot)1(dot)905+novice(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: cron job failing with 'database "xyz" is being accessed by other users' errors
Date: 2006-07-13 00:49:03
Message-ID: 4d6d2c130607121749t39aa02a0sf9f51585b936966f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
[ NB: I posted this several hours ago, but it never appeared.  Below
is essentially the same post, but I corrected a significant oversight.
]

Hi everyone.

I have a Perl/DBI script, which runs as a monthly cron job; it
downloads data from a remote site and uses it to rebuild a local
database (let's call this DB "xyz").  The script first builds the new
database under a temporary name ( e.g. "xyz_tmp"), and when the newly
built DB passes all the tests, it drops the old version and renames
the new version to "xyz".

The script works fine most of the time, but it fails occasionally with
the error

 ERROR: 'database "xyz" is being accessed by other users

This is always due to the fact that, despite the reminders that get
sent every month to all the users of "xyz", one of them has left a
psql session, connected to "xyz", running overnight (the script runs
at 2 AM, as a cron job).

The only way I know around this is to kill and restart the Pg server
before running the script, thereby killing *all* ongoing sessions
(even those not connected to "xyz").

Is there a more "civilized"/clueful approach?

[Here's the bit I missed in my original post.]

More importantly, killing and restarting the server requires that the
cron job be run by the postgres superuser, which I would like to
avoid.  Instead, the job is being run by a user that can connect as
the owner of "xyz".  Is there a way for the owner of "xyz" to
disconnect all the other users connecting to "xyz"?

Thanks!

tlm

PS:  Is there a better list to send this query to than psql-novice?

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2006-07-13 03:42:54
Subject: The name of the game (was Re: postgre linkage with non-postgre db)
Previous:From: Bruno Wolff IIIDate: 2006-07-12 19:19:55
Subject: Re: increment counter in VIEW

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group