BUG #15493: Wrong name of fields/missing fields for the internal statistic

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15493: Wrong name of fields/missing fields for the internal statistic

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15493
Logged by:          - -
Email address:      [hidden email]
PostgreSQL version: 11.0
Operating system:   CentOS 7.5
Description:        

How to produce:
- Simple install pgsql 11 on CentOS from yum repo of postgresql.org
- init the cluster
- start the server
- wait some time

After some times, many errors are logged about not existing fields.
< 2018-11-08 14:21:59.820 CET >ERROR:  column "procpid" does not exist at
character 132
< 2018-11-08 14:21:59.820 CET >STATEMENT:  SELECT datname, datid, usename,
client_addr, '' AS state,    COALESCE(ROUND(EXTRACT(epoch FROM
now()-query_start)),0) AS seconds,
         procpid as pid, regexp_replace(current_query, E'[\n\r\u2028]+', '
', 'g' ) AS current_query FROM pg_stat_activity WHERE (query_start IS NOT
NULL AND current_query NOT LIKE '<IDLE>%') ORDER BY query_start, procpid
DESC;
< 2018-11-08 14:22:00.117 CET >ERROR:  column "current_query" does not exist
at character 200
< 2018-11-08 14:22:00.117 CET >STATEMENT:  SELECT COUNT(datid) AS current,
          (SELECT setting AS mc FROM pg_settings WHERE name =
'max_connections') AS mc,
          d.datname
        FROM pg_database d
        LEFT JOIN pg_stat_activity s ON (s.datid = d.oid) WHERE
current_query <> '<IDLE>'
        GROUP BY 2,3
        ORDER BY datname

When I look in the database I can see, that the field has other name:
postgres=# \d+ pg_stat_activity
                                  View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable |
Default | Storage  | Description
------------------+--------------------------+-----------+----------+---------+----------+-------------
 datid            | oid                      |           |          |      
 | plain    |
 datname          | name                     |           |          |      
 | plain    |
 pid              | integer                  |           |          |      
 | plain    |

And pg_settings has no column current_query:
postgres=# \d+ pg_settings    
                            View "pg_catalog.pg_settings"
     Column      |  Type   | Collation | Nullable | Default | Storage  |
Description
-----------------+---------+-----------+----------+---------+----------+-------------
 name            | text    |           |          |         | extended |
 setting         | text    |           |          |         | extended |
 unit            | text    |           |          |         | extended |
 category        | text    |           |          |         | extended |
 short_desc      | text    |           |          |         | extended |
 extra_desc      | text    |           |          |         | extended |
 context         | text    |           |          |         | extended |
 vartype         | text    |           |          |         | extended |
 source          | text    |           |          |         | extended |
 min_val         | text    |           |          |         | extended |
 max_val         | text    |           |          |         | extended |
 enumvals        | text[]  |           |          |         | extended |
 boot_val        | text    |           |          |         | extended |
 reset_val       | text    |           |          |         | extended |
 sourcefile      | text    |           |          |         | extended |
 sourceline      | integer |           |          |         | plain    |
 pending_restart | boolean |           |          |         | plain    |

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15493: Wrong name of fields/missing fields for the internal statistic

Jeff Janes
On Thu, Nov 8, 2018 at 9:12 AM PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15493
Logged by:          - -
Email address:      [hidden email]
PostgreSQL version: 11.0
Operating system:   CentOS 7.5
Description:       

How to produce:
- Simple install pgsql 11 on CentOS from yum repo of postgresql.org
- init the cluster
- start the server
- wait some time

The query you show is not executed merely due to the passage of time.  Something is executing that query.  It is probably a monitoring tool.

Since procpid was removed back in version 9.2, the monitoring tools seems massively out of date.  It is possible this tool has buggy code to detect the version, and concludes that v11 is prior to 9.2.  But that seems unlikely to be broken by v11 but not by v10.  Do you have the same problem in v10?

Figure out what your monitoring tool is and report the bug to its maintainer.

Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15493: Wrong name of fields/missing fields for the internal statistic

Tom Lane-2
In reply to this post by PG Bug reporting form
=?utf-8?q?PG_Bug_reporting_form?= <[hidden email]> writes:

> After some times, many errors are logged about not existing fields.
> < 2018-11-08 14:21:59.820 CET >ERROR:  column "procpid" does not exist at
> character 132
> < 2018-11-08 14:21:59.820 CET >STATEMENT:  SELECT datname, datid, usename,
> client_addr, '' AS state,    COALESCE(ROUND(EXTRACT(epoch FROM
> now()-query_start)),0) AS seconds,
>          procpid as pid, regexp_replace(current_query, E'[\n\r\u2028]+', '
> ', 'g' ) AS current_query FROM pg_stat_activity WHERE (query_start IS NOT
> NULL AND current_query NOT LIKE '<IDLE>%') ORDER BY query_start, procpid
> DESC;

The procpid and current_query columns disappeared from pg_stat_activity in
Postgres 9.2 (or more accurately, they were renamed because they changed
meaning a bit).  Apparently you're using some very old monitoring software
that hasn't been updated to know about that.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15493: Wrong name of fields/missing fields for the internal statistic

Frank Büttner
Hi Tom,
thanks for the hint.
In the background there was an extreme old monitoring script running.
But I didn't know about it. Now it is fixed.

Regards,
Frank


Am 08.11.2018 um 15:48 schrieb Tom Lane:

> =?utf-8?q?PG_Bug_reporting_form?= <[hidden email]> writes:
>> After some times, many errors are logged about not existing fields.
>> < 2018-11-08 14:21:59.820 CET >ERROR:  column "procpid" does not exist at
>> character 132
>> < 2018-11-08 14:21:59.820 CET >STATEMENT:  SELECT datname, datid, usename,
>> client_addr, '' AS state,    COALESCE(ROUND(EXTRACT(epoch FROM
>> now()-query_start)),0) AS seconds,
>>          procpid as pid, regexp_replace(current_query, E'[\n\r\u2028]+', '
>> ', 'g' ) AS current_query FROM pg_stat_activity WHERE (query_start IS NOT
>> NULL AND current_query NOT LIKE '<IDLE>%') ORDER BY query_start, procpid
>> DESC;
>
> The procpid and current_query columns disappeared from pg_stat_activity in
> Postgres 9.2 (or more accurately, they were renamed because they changed
> meaning a bit).  Apparently you're using some very old monitoring software
> that hasn't been updated to know about that.
>
> regards, tom lane
>