How to force disconnections from a Perl script?

On Mon, Feb 19, 2007 at 11:10:55AM -0500, Kynn Jones wrote:

The script is quite solid and has been performing flawlessly for
several months now, with one exception: it fails irrecoverably
whenever some user forgets to disconnect from mydb at the time that
the script attempts to delete it (or rename it, for that matter). The
error is "ERROR: database "mydb" is being accessed by other users".

Why irrecoverably? If the command fails, you just wait and try it
again.

You could use the pg_stat tables to work out who is connected and use
pg_cancel_backend() to kill them. You could "kill -INT" them yourself.
You could change the pg_hba.conf to forbid logging in and then bouncing
the server.

Hope this gives you some ideas.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Why irrecoverably? If the command fails, you just wait and try it
again.

You could use the pg_stat tables to work out who is connected and use
pg_cancel_backend() to kill them. You could "kill -INT" them yourself.
You could change the pg_hba.conf to forbid logging in and then bouncing
the server.

I was going to suggest the same things you did, but then I thought
better... the OP is running the thing at night from a client box, not on
the DB, so restart and process listing is probably off limits...

There's 0 chance somebody will close at midnight it's open connection
forgotten when he left office, so wait and retry would not do any good
;-)

And pg_stat will only show you running queries, not the idle
connections.

If you only could list all the connection's pids in a client you could
loop and kill them all. Of course the loop would kill itself too if not
careful enough ;-)

Cheers,
Csaba.

Hi,

you could let the script look into the output of "ps aux". Open idle connections are usually show like this:

postgres 18383 0.0 0.6 18596 4900 ? Ss 16:38 0:00 postgres: dbuser database hostname(39784) idle in transaction

Then you can simply collect the PIDs and kill these processes (just kill, not with "-9"). If there are no demons lurking behind them reestablishing the connections, this is a quite reliable way to get rid of connections in a graceful way.

Regards, Frank.

On Mon, 19 Feb 2007 11:10:55 -0500 "Kynn Jones" <kynnjo@gmail.com> thought long, then sat down and wrote:

I have a Perl script that runs every night and updates a local Pg
database, sitting on a Linux server. (I'll refer to this database as
"mydb" in the following.)

The update process takes about 1 hour, so the script first builds a
temporary database called mydb_tmp. Once mydb_tmp is built and passes
a battery of tests, the script deletes mydb and renames mydb_tmp to
mydb.

The script is quite solid and has been performing flawlessly for
several months now, with one exception: it fails irrecoverably
whenever some user forgets to disconnect from mydb at the time that
the script attempts to delete it (or rename it, for that matter). The
error is "ERROR: database "mydb" is being accessed by other users".

Now, we, the users of mydb, know very well that we should disconnect
from it at the end of the day, but inevitably one of us forgets
(including myself on occasion, I'm sorry to admit).

My question is, how can I make the script handle this situation more
robustly? (At the moment I do get an email message alerting to this
failure when it happens, but I'd like to eliminate this type of
failure altogether. It is, after all, a pretty silly reason for this
script to fail.)

The ideal solution, from my point of view, would be for the script to
forcibly disconnect everyone from mydb at the time of updating it,
maybe sending a warning a minute or so beforehand, but I have not hit
upon a way to do this. (I should point out that, in the case of this
particular database, mydb, such forcible disconnections would cause no
major disruption to anyone.)

I would greatly appreciate your ideas and suggestions.

FWIW, the script is currently run by my uid, but I could have it run
by the postgres user, if that's of any help here.

Thanks in advance!

kj

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606 Mail: frank.finner@invenius.de
Telefax: 0271 231 8608 Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651

Csaba Nagy <nagy@ecircle-ag.com> writes:

And pg_stat will only show you running queries, not the idle
connections.

Nonsense. pg_stat_activity + "kill -TERM" should solve this problem
reasonably well. Some of us don't trust kill -TERM 100%, which is why
it's not currently exposed as a standard function, but if you're using
a reasonably recent PG release it's probably safe.

regards, tom lane

On Tue, 2007-02-20 at 03:43, Tom Lane wrote:

Nonsense. pg_stat_activity + "kill -TERM" should solve this problem
reasonably well. Some of us don't trust kill -TERM 100%, which is why
it's not currently exposed as a standard function, but if you're using
a reasonably recent PG release it's probably safe.

Oh, OK... so pg_stat_activity shows all connections now ? Or it was
always like that ? For some reason I thought it will only show
connections where a transaction is in progress. Idle can mean there is
nothing executing at all... are those shown too ?

Cheers,
Csaba.