--collector.long_running_transactions returns collector error when enabled

[kalEl2001]

What did you do?
I enabled --collector.long_running_transactions flag

What did you expect to see?
Collector collected the expected long running transaction stats

What did you see instead? Under which circumstances?
I get this error on the log

time=2025-09-29T09:34:13.791Z level=ERROR source=collector.go:207 msg="collector failed" name=long_running_transactions duration_seconds=0.031405708 err="sql: Scan error on column index 1, name \"oldest_timestamp_seconds\": converting NULL to float64 is unsupported"

Environment
The exporter is running inside a Kubernetes cluster and the PostgreSQL DB is a GCP CloudSQL instance.

• System information:

    `Linux 6.6.97+ x86_64`
  

• postgres_exporter version:

postgres_exporter, version 0.18.0 (branch: HEAD, revision: e62fe086f0dc5a0866d4af494d34e0e3de0b16d7)
  build user:       root@f5e839e095ef
  build date:       20250925-19:45:01
  go version:       go1.24.7
  platform:         linux/amd64
  tags:             unknown

• postgres_exporter flags:

--collector.stat_statements
--collector.stat_statements.include_query
--collector.long_running_transactions

• PostgreSQL version:

  16

• Logs:

time=2025-09-29T09:34:13.791Z level=ERROR source=collector.go:207 msg="collector failed" name=long_running_transactions duration_seconds=0.031405708 err="sql: Scan error on column index 1, name \"oldest_timestamp_seconds\": converting NULL to float64 is unsupported"

[neopolis]

Yes, I have the same error with PostgreSQL 17.6 version and postgres_exporter 0.18.1 version:

postgres_exporter[254973]: time=2025-10-03T13:35:24.272+02:00 level=ERROR source=collector.go:207 msg="collector failed" name=long_running_transactions duration_seconds=0.070271617 err="sql: Scan error on column index 1, name "oldest_timestamp_seconds": converting NULL to float64 is unsupported"

There is no such error with older postgres_exporter 0.17.1 version.
Could you check --collector.long_running_transactions and fix it? Thanks

[PhilippGramzow]

Looks like a permission issue.

The Query used in this collector

SELECT                                                              
    COUNT(*) as transactions,
    MAX(EXTRACT(EPOCH FROM clock_timestamp() - pg_stat_activity.xact_start)) AS oldest_timestamp_seconds
FROM pg_catalog.pg_stat_activity
WHERE state IS DISTINCT FROM 'idle'
AND query NOT LIKE 'autovacuum:%'
AND pg_stat_activity.xact_start IS NOT NULL
AND pid <> pg_backend_pid();

always returns null for column xact_start when using a non-superuser.

If you add superuser privileges to your monitoring user it should work, but I would not recommend this.

[dmitry-sinina]

it returns NULL when no active transactions found:

# SELECT                                                              
    COUNT(*) as transactions,
    MAX(EXTRACT(EPOCH FROM clock_timestamp() - pg_stat_activity.xact_start)) AS oldest_timestamp_seconds
FROM pg_catalog.pg_stat_activity
WHERE state IS DISTINCT FROM 'idle'
AND query NOT LIKE 'autovacuum:%'
AND pg_stat_activity.xact_start IS NOT NULL
AND pid <> pg_backend_pid();
 transactions | oldest_timestamp_seconds 
--------------+--------------------------
            0 |                         
(1 row)

[JimStavr]

Having the same issue with 0.18.1 version.

Maybe change to:
COALESCE(MAX(EXTRACT(EPOCH FROM clock_timestamp() - pg_stat_activity.xact_start)), 0) AS oldest_timestamp_seconds
So it will return 0 , when no active transactions found?