Several of the new collectors fail with incorrect data types

[sclu1034]

What did you do?
Updated to v0.13.0 and removed queries.yaml in favor of the built-in collectors.

What did you expect to see?
An error-free log and the same metrics to be reported as before.

What did you see instead? Under which circumstances?

Repeating entries of the logs shown below and affected metrics, such as pg_postmaster_start_time_seconds, stopped being reported.
No special circumstances, I just started the tool via quay.io/prometheuscommunity/postgres-exporter:latest with the exact same setup as the previous image.

Environment

• postgres_exporter version:

postgres_exporter, version 0.13.0 (branch: HEAD, revision: f550f8f8ad77e054f12eb3d3f6605edc037573f5)
  build user:       root@de207ed6b751
  build date:       20230622-07:05:45
  go version:       go1.20.5
  platform:         linux/amd64
  tags:             netgo static_build

• postgres_exporter flags: Default Docker CMD line

• PostgreSQL version: 15.3

• Logs:

ts=2023-06-22T08:25:13.132Z caller=proc.go:250 msg="Excluded databases" databases=[]
ts=2023-06-22T08:25:13.142Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9187
ts=2023-06-22T08:25:13.142Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9187
ts=2023-06-22T08:25:17.855Z caller=server.go:74 level=info msg="Established new database connection" fingerprint=postgres:5432
ts=2023-06-22T08:25:17.860Z caller=collector.go:190 level=error msg="collector failed" name=process_idle duration_seconds=0.004954557 err="sql: Scan error on column index 3, name \"seconds\": unsupported Scan, storing driver.Value type []uint8 into type *[]int64"
ts=2023-06-22T08:25:17.861Z caller=postgres_exporter.go:622 level=info msg="Semantic version changed" server=postgres:5432 from=0.0.0 to=15.3.0
ts=2023-06-22T08:25:17.864Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.008552172 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-19 13:22:47.713957 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-22T08:25:17.865Z caller=collector.go:190 level=error msg="collector failed" name=stat_database duration_seconds=0.009474908 err="sql: Scan error on column index 1, name \"datname\": converting NULL to string is unsupported"
ts=2023-06-22T08:25:17.867Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.011338687 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-19 13:22:47.713957 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-22T08:25:32.855Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.000977326 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-19 13:22:47.713957 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-22T08:25:32.857Z caller=collector.go:190 level=error msg="collector failed" name=process_idle duration_seconds=0.002956973 err="sql: Scan error on column index 3, name \"seconds\": unsupported Scan, storing driver.Value type []uint8 into type *[]int64"
ts=2023-06-22T08:25:32.859Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.00465972 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-19 13:22:47.713957 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-22T08:25:32.859Z caller=collector.go:190 level=error msg="collector failed" name=stat_database duration_seconds=0.005185534 err="sql: Scan error on column index 1, name \"datname\": converting NULL to string is unsupported"
ts=2023-06-22T08:25:47.856Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.001654959 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-19 13:22:47.713957 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-22T08:25:47.857Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.002426217 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-19 13:22:47.713957 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-22T08:25:47.857Z caller=collector.go:190 level=error msg="collector failed" name=stat_database duration_seconds=0.00275669 err="sql: Scan error on column index 1, name \"datname\": converting NULL to string is unsupported"
ts=2023-06-22T08:25:47.859Z caller=collector.go:190 level=error msg="collector failed" name=process_idle duration_seconds=0.004420991 err="sql: Scan error on column index 3, name \"seconds\": unsupported Scan, storing driver.Value type []uint8 into type *[]int64"

[SuperQ]

CC @Sticksman

[reneeckstein]

can confirm the same issue on PostgreSQL 14.7 (AWS Aurora Postgres)

[Art-Sel]

same problem on 14.2

postgres_exporter, version 0.13.0 (branch: master, revision: f550f8f)
build user: user@lenovo
build date: 20230623-13:42:32
go version: go1.18.3
platform: linux/amd64
tags: netgo static_build

ts=2023-06-23T14:19:50.408Z caller=server.go:74 level=info msg="Established new database connection" fingerprint=/var/run/postgresql/:5432
ts=2023-06-23T14:19:50.412Z caller=postgres_exporter.go:622 level=info msg="Semantic version changed" server=/var/run/postgresql/:5432 from=0.0.0 to=14.2.0
ts=2023-06-23T14:19:50.421Z caller=collector.go:190 level=error msg="collector failed" name=stat_database duration_seconds=0.012660037 err="sql: Scan error on column index 1, name \"datname\": converting NULL to string is unsupported"
ts=2023-06-23T14:19:50.426Z caller=collector.go:190 level=error msg="collector failed" name=statio_user_tables duration_seconds=0.017781942 err="sql: Scan error on column index 5, name \"idx_blks_read\": converting NULL to int64 is unsupported"
ts=2023-06-23T14:19:50.426Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.018095918 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-23 16:48:04.349714 +0300 MSK\") to a float64: invalid syntax"
ts=2023-06-23T14:19:50.430Z caller=collector.go:190 level=error msg="collector failed" name=process_idle duration_seconds=0.021661276 err="sql: Scan error on column index 3, name \"seconds\": unsupported Scan, storing driver.Value type []uint8 into type *[]int64"
ts=2023-06-23T14:19:50.432Z caller=collector.go:190 level=error msg="collector failed" name=stat_user_tables duration_seconds=0.024134202 err="sql: Scan error on column index 5, name \"idx_scan\": converting NULL to int64 is unsupported"
ts=2023-06-23T14:19:50.434Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.025337841 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-23 16:48:04.349714 +0300 MSK\") to a float64: invalid syntax"
ts=2023-06-23T14:19:51.237Z caller=collector.go:190 level=error msg="collector failed" name=statio_user_tables duration_seconds=0.012568834 err="sql: Scan error on column index 5, name \"idx_blks_read\": converting NULL to int64 is unsupported"
ts=2023-06-23T14:19:51.239Z caller=collector.go:190 level=error msg="collector failed" name=process_idle duration_seconds=0.014648358 err="sql: Scan error on column index 3, name \"seconds\": unsupported Scan, storing driver.Value type []uint8 into type *[]int64"
ts=2023-06-23T14:19:51.242Z caller=collector.go:190 level=error msg="collector failed" name=stat_user_tables duration_seconds=0.017622653 err="sql: Scan error on column index 5, name \"idx_scan\": converting NULL to int64 is unsupported"
ts=2023-06-23T14:19:51.243Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.017891161 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-23 16:48:04.349714 +0300 MSK\") to a float64: invalid syntax"
ts=2023-06-23T14:19:51.244Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.019340401 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-23 16:48:04.349714 +0300 MSK\") to a float64: invalid syntax"
ts=2023-06-23T14:19:51.245Z caller=collector.go:190 level=error msg="collector failed" name=stat_database duration_seconds=0.020069416 err="sql: Scan error on column index 1, name \"datname\": converting NULL to string is unsupported"
ts=2023-06-23T14:20:39.361Z caller=collector.go:190 level=error msg="collector failed" name=statio_user_tables duration_seconds=0.012915492 err="sql: Scan error on column index 5, name \"idx_blks_read\": converting NULL to int64 is unsupported"
ts=2023-06-23T14:20:39.363Z caller=collector.go:190 level=error msg="collector failed" name=process_idle duration_seconds=0.014780197 err="sql: Scan error on column index 3, name \"seconds\": unsupported Scan, storing driver.Value type []uint8 into type *[]int64"
ts=2023-06-23T14:20:39.363Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.015138929 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-23 16:48:04.349714 +0300 MSK\") to a float64: invalid syntax"
ts=2023-06-23T14:20:39.365Z caller=collector.go:190 level=error msg="collector failed" name=stat_user_tables duration_seconds=0.017022539 err="sql: Scan error on column index 5, name \"idx_scan\": converting NULL to int64 is unsupported"
ts=2023-06-23T14:20:39.367Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.01827618 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-23 16:48:04.349714 +0300 MSK\") to a float64: invalid syntax"
ts=2023-06-23T14:20:39.369Z caller=collector.go:190 level=error msg="collector failed" name=stat_database duration_seconds=0.021057372 err="sql: Scan error on column index 1, name \"datname\": converting NULL to string is unsupported"

[Sticksman]

Taking a look

[moonchildua]

same problem with 12.5

[SuperQ]

If anyone wants to test these build artifacts from the #823 that would help.

[sclu1034]

This is not fixed!

Just updated to v0.13.1. There are less failures, but some of the previous ones still exist.
Specifically, ones that have no relation to NULL values:

ts=2023-06-27T13:56:02.460Z caller=proc.go:250 msg="Excluded databases" databases=[]
ts=2023-06-27T13:56:02.471Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9187
ts=2023-06-27T13:56:02.471Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9187
ts=2023-06-27T13:56:02.860Z caller=server.go:74 level=info msg="Established new database connection" fingerprint=postgres:5432
ts=2023-06-27T13:56:02.868Z caller=postgres_exporter.go:622 level=info msg="Semantic version changed" server=postgres:5432 from=0.0.0 to=15.3.0
ts=2023-06-27T13:56:02.876Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.015816516 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-27 11:39:22.518996 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-27T13:56:02.878Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.01693146 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-27 11:39:22.518996 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-27T13:56:17.863Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.002038289 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-27 11:39:22.518996 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-27T13:56:17.864Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.003329763 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-27 11:39:22.518996 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-27T13:56:32.863Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.002265238 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-27 11:39:22.518996 +0200 CEST\") to a float64: invalid syntax"
ts=2023-06-27T13:56:32.865Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.004408598 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-06-27 11:39:22.518996 +0200 CEST\") to a float64: invalid syntax"

[mrjones-plip]

@sclu1034 - if you try running on master, is it fixed? I found that running on latest (v0.13.1) it wasn't fixed but that on master it was. I run in docker compose, so for me this was image: prometheuscommunity/postgres-exporter:master

I wonder if v0.13.1 didn't have the fix included in the PR somehow 🤷

[Sticksman]

Yeah I think we got the next set of fixes in after 0.13.1 got released