sql query result and sql_exporter metrics are not equal

[ridvankorkmaz]

Describe the bug
sql query result and sql_exporter metrics are not equal.

To Reproduce

scraped results

postgres@host-1 ~ $ curl localhost:9399/metrics 2>/dev/null | grep 'pg_settings_source_cnt'
# HELP pg_settings_source_cnt Number of settings
# TYPE pg_settings_source_cnt gauge
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="/etc/postgresql-15/postgresql.conf",target="database1"} 82
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="default",target="database1"} 280

query use by sql_exporter to get metrics

database1=# select coalesce(sourcefile, 'default') as sourcefile, count(*) as cnt from pg_settings group by sourcefile;
             sourcefile             | cnt 
------------------------------------+-----
 default                            | 273
 /etc/postgresql-15/postgresql.conf |  84
(2 rows)

Expected behavior
Scraped results and query results are expected to be same.
scrape sourcefile.default is 280
query sourcefile.default is 273

scrape sourcefile."/etc/postgresql-15/postgresql.conf" is 82
query sourcefile."/etc/postgresql-15/postgresql.conf" is 84

Configuration
Metric definition

  - metric_name: pg_settings_source_cnt
    type: gauge
    help: Number of settings
    values: [cnt]
    static_labels:
      slave: False
    key_labels: [sourcefile]
    query_ref: pg_settings_source

.....
....
....

  - query_name: pg_settings_source
    query: |
      select
          coalesce(sourcefile, 'default') as sourcefile,
          count(*) as cnt
      from pg_settings group by sourcefile

sql_exporter.yml

global:
  # If scrape_timeout <= 0, no timeout is set unless Prometheus provides one. The default is 10s.
  scrape_timeout: 30s
  # Subtracted from Prometheus' scrape_timeout to give us some headroom and prevent Prometheus from timing out first.
  scrape_timeout_offset: 500ms
  # Minimum interval between collector runs: by default (0s) collectors are executed on every scrape.
  min_interval: 0s
  # Maximum number of open connections to any one target. Metric queries will run concurrently on multiple connections,
  # as will concurrent scrapes.
  max_connections: 3
  # Maximum number of idle connections to any one target. Unless you use very long collection intervals, this should
  # always be the same as max_connections.
  max_idle_connections: 3
  # Maximum number of maximum amount of time a connection may be reused. Expired connections may be closed lazily before reuse.
  # If 0, connections are not closed due to a connection's age.
  max_connection_lifetime: 5m

  # Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)
  # the schema gets dropped or replaced to match the driver expected DSN format.

jobs:
  - job_name: postgresql_common_queries
    collectors: ['postgresql_common_queries']
    static_configs:
      - targets:
          database1: 'postgresql://monuser@localhost:5432/database1?sslmode=disable'


  # Collectors (referenced by name) to execute on the target.
  # Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
  # collectors: [postgresql_*]

collector_files: 
  - "*.collector.yml"

Additional context
Add any other context about the problem here.

[burningalchemist]

Hey @ridvankorkmaz, thanks for your report. Interesting issue. There is no custom query handling on the sql_exporter side, but I'm happy to look into that. I believe I can reproduce the case on my end. 👍

In the meantime, there are some steps you might try:

• sql_exporter also has another driver for PostgreSQL embedded (pgx), so you can try send a query using this one (replace postgres:// schema with pgx://). I'm curious if using a different driver would help or not;
• try some other query to help me understand the scope better;
• it would be great if you could also test your query with xo/usql (https://github.com/xo/usql) cli sql client.

[burningalchemist]

Hey @ridvankorkmaz,

So far I'm unable to reproduce the case on my end (I've taken your provided configuration):

database_dev=>  select coalesce(sourcefile, 'default') as sourcefile, count(*) as cnt from pg_settings group by sourcefile;
 sourcefile | cnt
------------+-----
 default    | 392
(1 row)

database_dev=> select count(*) from pg_settings;
 count
-------
   392
(1 row)

root@database-dev:/root# curl localhost:9399/metrics 2>/dev/null | grep 'pg_settings_source_cnt'
# HELP pg_settings_source_cnt Number of settings
# TYPE pg_settings_source_cnt gauge
pg_settings_source_cnt{slave="False",sourcefile="default"} 392

It's PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.3.1, 64-bit on my end.

After inspecting your query, I also think it might be potentially related to permissions sql_exporter user is given. pg_settings is a view which displays the settings for the current session, and it contains configuration parameters, some of which are restricted based on the user querying them. Certain parameters are only visible to superusers because they might contain sensitive information or because they require superuser privileges to view or modify. So to debug further I'd try checking the permissions and maybe trying different users.

[burningalchemist]

Let's proceed in the discussion as it's unrelated to the exporter's functional part, at least so far.