I checked the FAQ and docs but haven't found anything definitive. This
is my SQL test script:
SELECT pg_backend_pid();
SELECT * FROM pg_stat_activity order by procpid;
When I run psql reading that I find that my backend procpid is not in
the list. I know that I can see it if I can introduce a little sleep (1
second) between the connection and the reading of pg_stat_activity.
My question is this. Is there a more reliable way to see if a
particular backend is still alive? I am trying to do a locking system
and this is necessary to make it work. I think that in actual sessions
I will be OK but my unit test fails most of the time because of this.
I will explain my scheme in case there is a better way to do what I
want. Note that using transactions won't work in my application because
I never know if I am already in one or not or if one will start while I
am in this process. What I do is create a table that has, among other
things, a serial, primary key and a pid_lock field. I add a rule like
this:
CREATE OR REPLACE RULE session_pid_lock AS ON UPDATE TO session WHERE EXISTS ( SELECT 1 FROM pg_stat_activity
WHEREpg_stat_activity.procpid = old.pid_lock AND pg_stat_activity.procpid != pg_backend_pid()) DO INSTEAD NOTHING;
Now all I do to grab the lock is update pid_lock with my own backend
PID. I then test it to see if it has mine or someone else's. Now I
know whether to fail, retry or whatever. As soon as the first DB
connection drops I can grab the lock.
Right now I am modifying my lock class (It's in Python) to add a 1
second sleep before trying to grab an existing session. That seems to
work but I hope I can do better.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
