Selecting Non-Existent Columns

Since we run a bunch of postgres instances on several different versions it has become somewhat of a chore to update my monitoring to work with each new version of PostgreSQL that gets released.

As postgres evolves the system views change to accommodate new features or provide more insight into its operations, which usually means column changes to existing system views.  I had to add condition logic in my monitoring to run the correct version of a query for the correct version of postgres.

Well… I used to have to do that.

A few weeks ago I stumbled across a post (I wish I had bookmarked it, cuz I can’t find it now) about how to write a SQL query statement that will work even if it is used against tables where columns may or may not exist or be different.

It’s something developers use in code every day, but I had never thought about using it with SQL… SCOPE RULES!

light bulb

Yup, scope rules exist in SQL too!  If a column isn’t found in a sub-query, it will look in the outer query for a matching column.

For example, as part of my monitoring I want to monitor the number of threads waiting on locks. Starting with PostgreSQL v8.2 the boolean “waiting” column in the “pg_stat_activity” view could be used to easily query how many threads were waiting on a lock. But in v9.6 that column was replaced with two columns, “wait_event” and “wait_event_type”.

Using scope rules I can easily accommodate the change in SQL without the need to add condition coding or interrogate the postgres version or any of that nonsense.

Here’s an example:

-- lock wait count on PG v8.2 thru v9.5
select (select count(*)
          from pg_stat_activity
         where waiting = true)

-- add lock wait count on PG v9.6 and up
    +  (select count(*)
          from pg_stat_activity
         where wait_event_type is not null)

    as wait_count

-- inherit one of the below columns
from (select false as waiting
           , null  as wait_event_type
     ) as dummy
;

Now it doesn’t matter what version of postgres I query, I get the correct result.

In the example above when the column doesn’t exist in the “pg_stat_activity” view, then the column and its value are inherited from the outer “dummy” table.

You need to be careful about choosing what values you inherit so they make sense for your query, but in my case it works perfectly.

Advertisements