Thread: View pg_stat_activity slow to get up to date
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.
On 11/8/2004 12:03 PM, D'Arcy J.M. Cain wrote: > 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. That is because the way your backend gets the information from the pgstat daemon process is by reading a file, which is at maximum written by that process every 500 msec. You will hardly ever see your own query. > > 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. All these statistics are unreliable "by design", as explained numerous times on this list. In short, a backend should never be slowed down because the pgstat process can't swallow the stats fast enough. That is the reason for using UDP in the first place. So the kernel is allowed to drop stats packets, but not to block a backend. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Mon, 08 Nov 2004 12:56:57 -0500 Jan Wieck <JanWieck@Yahoo.com> wrote: Hi Jan. > On 11/8/2004 12:03 PM, D'Arcy J.M. Cain wrote: > > > 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. > > That is because the way your backend gets the information from the > pgstat daemon process is by reading a file, which is at maximum > written by that process every 500 msec. You will hardly ever see your > own query. OK, I thought that using stats might not be the most reliable method but I couldn't find any other. Is there a reliable way to get that info? Note that I don't care what the backend is doing, just whether it is still running or not. > All these statistics are unreliable "by design", as explained numerous > > times on this list. In short, a backend should never be slowed down > because the pgstat process can't swallow the stats fast enough. That > is the reason for using UDP in the first place. So the kernel is > allowed to drop stats packets, but not to block a backend. So it is worse than I thought. I thought that it was slow but it looks like it may never even make it. Probably OK for my purposes but it would be nice to have a reliable method. -- 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.
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> 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.
ISTM that what you have here is a bad substitute for using user locks
(see contrib/userlock/).
regards, tom lane
On Mon, 08 Nov 2004 13:07:34 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > "D'Arcy J.M. Cain" <darcy@druid.net> writes: > > 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. > > ISTM that what you have here is a bad substitute for using user locks > (see contrib/userlock/). Perhaps. I assume that the lock is automatically released when the holder closes its connection to the database, right? If so then that's what I need. -- 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.
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ISTM that what you have here is a bad substitute for using user locks
>> (see contrib/userlock/).
> Perhaps. I assume that the lock is automatically released when the
> holder closes its connection to the database, right? If so then that's
> what I need.
Right, user locks will be dropped automatically at backend exit
(otherwise their management is entirely in your hands).
regards, tom lane