[BUG] Failures to query pg_stat_statements_info in postgres apm integration with specified dbname

[joesteffee]

Agent Environment Cluster Agent 7.49.1 - Commit: 1790cab - Serialization version: v5.0.97 - Go version: go1.20.10

Describe what happened: When using this configuration:

  - dbm: true
    dbname: <custom DB name>
    collect_activity_metrics: true
    collect_bloat_metrics: true
    collect_schemas:
      enabled: true
    query_samples:
      explain_parameterized_queries: true
    ignore_databases:
      - master
      - rdsadmin
      - postgres
    relations:
      - relation_regex: '.*'
        relkind:
          - r
          - i
          - t
          - s
          - p
    host: <redacted>

I receive frequent warnings: 2024-01-02 19:43:04 UTC | CORE | WARN | (pkg/collector/python/datadog_agent.go:131 in LogMessage) | postgres:6640f5283d2dae90 | (statements.py:370) | Failed to query for pg_stat_statements_info: relation "pg_stat_statements_info" does not exist LINE 1: SELECT dealloc FROM pg_stat_statements_info

The pg_stat_statements_info database only exists inside the 'postgres' database, but we have integrations set up to only query necessary databases. Need to either update the agent to allow specifying DB where this view is or update documentation to add this view to where the agent can pick it up.

Describe what you expected: Ability to set destination database of pg_stat_statements_info relation

Additional environment details (Operating System, Cloud provider, etc): AWS EKS with RDS

[sbquinlan]

See https://postgrespro.com/list/thread-id/2663111

You're probably running an old version of the extension on RDS. Running ALTER EXTENSION pg_stat_statements UPDATE TO '1.9'; should fix this.

I still think the "bug" tag here is valid. Datadog agent shouldn't assume specific versions of extensions.

[joesteffee]

@sbquinlan

I'm already running 1.9 which is the latest available in postgres 14.

postgres=> alter extension pg_stat_statements update to '1.9';
NOTICE:  version "1.9" of extension "pg_stat_statements" is already installed
ALTER EXTENSION

The 'public' schema is in my search path for the 'datadog' role:

SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

Datadog still can't find it in any of the target databases:

production_db=> select * from pg_stat_statements_info;
ERROR:  relation "pg_stat_statements_info" does not exist
LINE 1: select * from pg_stat_statements_info;
                      ^
production_db=> \c postgres
psql (14.10 (Homebrew), server 14.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "datadog".
postgres=> select * from pg_stat_statements_info;
 dealloc |         stats_reset
---------+------------------------------
   27400 | 2023-07-17 08:06:45.63264+00
(1 row)

[crertel-getthru]

Just seconding this...we see similar behavior.