Re: procpid? - Mailing list pgsql-hackers
| From | Gurjeet Singh |
|---|---|
| Subject | Re: procpid? |
| Date | |
| Msg-id | BANLkTikiUMi3koiYM3Py883TYwOFfMNLHw@mail.gmail.com Whole thread Raw |
| In response to | Re: procpid? (Bruce Momjian <bruce@momjian.us>) |
| List | pgsql-hackers |
On Tue, Jun 14, 2011 at 9:50 PM, Bruce Momjian <bruce@momjian.us> wrote:
FWIW, I wrote a monitoring query around it like this (the requirement was to not expose the current_query contents).
SELECT datname, procpid, usename, backend_start, xact_start, query_start,
waiting AS is_waiting, current_query = $$<IDLE>$$ AS is_idle,
current_query = $$<IDLE> in transaction$$ AS is_idle_in_transaction,
current_query ilike $$VACUUM%$$ as is_vacuum,
client_port IS NULL AND (current_query like $$autovacuum:%$$ OR current_query like $$VACUUM%$$) as is_autovacuum,
now() AS capture_time
FROM pg_catalog.pg_stat_activity
The tricky part was to determine how long a connection has been in the state that it currently is in. Since the various *_start columns are changed only as needed, I had to use the following expression to calculate that.
(capture_time - COALESCE(query_start, xact_start, backend_start))::interval
query_start is changed every time current_query value is changed; but it is NULL if the backend has just started. Similarly, xact_start changes whenever backend goes into/comes out of a transaction; but it is NULL when the backend has just started. backend_start is never NULL, so we can fall back on that when nothing else is available (i.e when the backend has just started).
If we separated is_idle and is_idle_in_transaction into separate fields, then we also need to somehow expose when did the backend get into that state, unless we promise to hold the assumptions true that were made when writing the above query (which is not as straightforward as one would expect).
Greg Smith wrote:Agreed on moving '<IDLE>' and '<IDLE> in transaction' into separate
> On 06/14/2011 06:00 PM, Tom Lane wrote:
> > As far as Greg's proposal is concerned, I don't see how a proposed
> > addition of two columns would justify renaming an existing column.
> > Additions should not break any sanely-implemented application, but
> > renamings certainly will.
> >
>
> It's not so much justification as something that makes the inevitable
> complaints easier to stomach, in terms of not leaving a really bad taste
> in the user's mouth. My thinking is that if we're going to mess with
> pg_stat_activity in a way that breaks something, I'd like to see it
> completely refactored for better usability in the process. If code
> breaks and the resulting investigation by the admin highlights something
> new, that offsets some of the bad user experience resulting from the
> breakage.
>
> Also, I haven't fully worked whether it makes sense to really change
> what current_query means if the idle/transaction component of it gets
> moved to another column. Would it be better to set current_query to
> null if you are idle, rather than the way it's currently overloaded with
> text in that case? I don't like the way this view works at all, but I'm
> not sure the best way to change it. Just changing procpid wouldn't be
> the only thing on the list though.
fields. If I had thought of it I would have done it that way years ago.
(At least I think it was me.) Using angle brackets to put magic values
in that field was clearly wrong.
FWIW, I wrote a monitoring query around it like this (the requirement was to not expose the current_query contents).
SELECT datname, procpid, usename, backend_start, xact_start, query_start,
waiting AS is_waiting, current_query = $$<IDLE>$$ AS is_idle,
current_query = $$<IDLE> in transaction$$ AS is_idle_in_transaction,
current_query ilike $$VACUUM%$$ as is_vacuum,
client_port IS NULL AND (current_query like $$autovacuum:%$$ OR current_query like $$VACUUM%$$) as is_autovacuum,
now() AS capture_time
FROM pg_catalog.pg_stat_activity
The tricky part was to determine how long a connection has been in the state that it currently is in. Since the various *_start columns are changed only as needed, I had to use the following expression to calculate that.
(capture_time - COALESCE(query_start, xact_start, backend_start))::interval
query_start is changed every time current_query value is changed; but it is NULL if the backend has just started. Similarly, xact_start changes whenever backend goes into/comes out of a transaction; but it is NULL when the backend has just started. backend_start is never NULL, so we can fall back on that when nothing else is available (i.e when the backend has just started).
If we separated is_idle and is_idle_in_transaction into separate fields, then we also need to somehow expose when did the backend get into that state, unless we promise to hold the assumptions true that were made when writing the above query (which is not as straightforward as one would expect).
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
pgsql-hackers by date: