Re: [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

This is an excellent feature, thanks!
But can we please keep the old boolean waiting column?
I see no reason to break backward-compatibility. Or maybe I'm missing something.

I just had to commit this to make our system run locally on 9.6:

commit 2e189f85fa56724bec5c5cab2fcf0d2f3a4ce22a
Author: Joel Jacobson <joel(at)trustly(dot)com>
Date: Fri Mar 11 08:19:52 2016 +0700

Make Have_Queries_Waiting() work with both <9.6 and >=9.6.

Apparently pg_stat_activity.waiting was removed by this commit:
commit 53be0b1add7064ca5db3cd884302dfc3268d884e
Author: Robert Haas <rhaas(at)postgresql(dot)org>
Date: Thu Mar 10 12:44:09 2016 -0500

Provide much better wait information in pg_stat_activity.

This forces us to do some ugly version checking to know which column to use.
I for one can think it would have been better to keep the old
boolean column,
which is not entirely useless as sometimes you just want to know
if something is
waiting and don't care about the details, then it's convenient to
have a boolean column
instead of having to write "wait_event IS NOT NULL".

Let's hope they will add back our dear waiting column so we can avoid this
ugly hack before upgrading to 9.6.

diff --git a/public/FUNCTIONS/have_queries_waiting.sql
b/public/FUNCTIONS/have_queries_waiting.sql
index d83e7c8..b54caf5 100644
--- a/public/FUNCTIONS/have_queries_waiting.sql
+++ b/public/FUNCTIONS/have_queries_waiting.sql
@@ -3,9 +3,16 @@ SET search_path TO 'public', pg_catalog;
CREATE OR REPLACE FUNCTION have_queries_waiting() RETURNS boolean
SECURITY DEFINER
SET search_path TO public, pg_temp
- LANGUAGE sql
+ LANGUAGE plpgsql
AS $$
- SELECT EXISTS (SELECT 1 FROM pg_stat_activity WHERE waiting)
+DECLARE
+BEGIN
+IF version() ~ '^PostgreSQL 9\.[1-5]' THEN
+ RETURN EXISTS (SELECT 1 FROM pg_stat_activity WHERE waiting);
+ELSE
+ RETURN EXISTS (SELECT 1 FROM pg_stat_activity WHERE wait_event IS
NOT NULL);
+END IF;
+END;
$$;

On Fri, Mar 11, 2016 at 6:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Or ... maybe this is intentional behavior? Now that I think about it,
>> doesn't each backend cache this info the first time its transaction
>> reads the data?
>
> Your view of pg_stat_activity is supposed to hold still within a
> transaction, yes. Otherwise it'd be really painful to do any complicated
> joins. I think there may be a function to explicitly flush the cache,
> if you really need to see intratransaction changes.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Joel Jacobson

Mobile: +46703603801
Trustly.com | Newsroom | LinkedIn | Twitter