Обсуждение: [GENERAL] column "waiting" does not exist

Hello

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Johann Spies
Sent: Donnerstag, 2. März 2017 09:28
To: pgsql-general@postgresql.org
Subject: [GENERAL] column "waiting" does not exist

I have no idea what is causing this message in the logs (PostgreSQL 9.6+177.pgdg80+1 on Debian):


2017-03-02 10:20:25 SAST [5196-1] [unknown] postgres postgres@template1 ERROR:  column "waiting" does not exist at
character217 
2017-03-02 10:20:25 SAST [5196-2] [unknown] postgres postgres@template1 STATEMENT:  SELECT tmp.mstate AS
state,COALESCE(count,0)FROM 
                         (VALUES ('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate)
                        LEFT JOIN
                         (SELECT CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle
intransaction%' THEN 'idle$ 
                         count(*) AS count
                         FROM pg_stat_activity WHERE pid != pg_backend_pid()  AND datname='data_portal'
                         GROUP BY CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle
intransaction%' THEN 'idl$ 
                         ) AS tmp2
                        ON tmp.mstate=tmp2.mstate
                        ORDER BY 1;
I do not know whether it is related but we recently get warnings about bloat in our system tables from the monitoring
program.
How do I fix the cause of this error message?

The name of the column in pg_stat_activity has changed. I assume it comes from there?:

9.3:

           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 datid            | oid                      |
 datname          | name                     |
 pid              | integer                  |
 usesysid         | oid                      |
 usename          | name                     |
 application_name | text                     |
 client_addr      | inet                     |
 client_hostname  | text                     |
 client_port      | integer                  |
 backend_start    | timestamp with time zone |
 xact_start       | timestamp with time zone |
 query_start      | timestamp with time zone |
 state_change     | timestamp with time zone |
 waiting          | boolean                  | <---
 state            | text                     |
 query            | text                     |

9.6:

           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 datid            | oid                      |
 datname          | name                     |
 pid              | integer                  |
 usesysid         | oid                      |
 usename          | name                     |
 application_name | text                     |
 client_addr      | inet                     |
 client_hostname  | text                     |
 client_port      | integer                  |
 backend_start    | timestamp with time zone |
 xact_start       | timestamp with time zone |
 query_start      | timestamp with time zone |
 state_change     | timestamp with time zone |
 wait_event_type  | text                     | <---
 wait_event       | text                     | <---
 state            | text                     |
 backend_xid      | xid                      |
 backend_xmin     | xid                      |
 query            | text                     |

Regards
Charles

Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)