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

Поиск
Список
Период
Сортировка

[GENERAL] column "waiting" does not exist

От
Johann Spies
Дата:
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 character 217
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 in transaction%' 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 in transaction%' 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?

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

Re: [GENERAL] column "waiting" does not exist

От
"Charles Clavadetscher"
Дата:
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)



Ynt: [GENERAL] column "waiting" does not exist

От
Neslisah Demirci
Дата:

Hi , 


Your monitoring program is trying to use pg_stat_activity's old version. In 9.6 pg_stat_activity has new columns named wait_event , wait_event_type . 


Which monitoring programme?


Neslişah Demirci | Veritabanı Yöneticisi 

Ayazağa cad. No:4 Uniq İstanbul Plaza
B2 /Kat:3 34396 Ayazağa-SARIYER-İstanbul
T. (+90) 212 453 16 00 – 5516
F. (+90) 212 453 16 16
www.markafoni.com
www.facebook.com/markafoni
blog.markafoni.com






Gönderen: Johann Spies <johann.spies@gmail.com> adına pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org>
Gönderildi: 2 Mart 2017 Perşembe 11:27
Kime: pgsql-general@postgresql.org
Konu: [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 character 217
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 in transaction%' 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 in transaction%' 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?

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

Re: [GENERAL] column "waiting" does not exist

От
Johann Spies
Дата:
Thanks Charles and Neslişah.


Charles Clavadetscher <clavadetscher@swisspug.org>  wrote:

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


On 2 March 2017 at 10:54, Neslisah Demirci <neslisah.demirci@markafoni.com> wrote:
 
Your monitoring program is trying to use pg_stat_activity's old version. In 9.6 pg_stat_activity has new columns named wait_event , wait_event_type . 

Which monitoring programme?

 Munin is the culprit.  We will update it soon.

Regards
Johann
 


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