function pg_current_wal_lsn() not exists on 9.5.x

[csabka]

bug in new feature in 0.9.0
pg_replication_slots pq: function pg_current_wal_lsn() does not exist" source="postgres_exporter.go:1503"

pg_current_wal_lsn() function only exists from 10 version.

There should be a bool to switch of this new feature

[roidelapluie]

thanks for your report. we could simply only query it in psql 10+

[nnz1024]

Hi @roidelapluie !
Still got this problem. It appears in

postgres_exporter/cmd/postgres_exporter/postgres_exporter.go

Line 435 in c51a28f

SELECT slot_name, database, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)

This query is marked as >=9.4.0 and contains pg_current_wal_lsn() which introduced only in Postgres 10.

[nicholasklem]

Any progress on reviewing this PR? 👍

[halvaborsch]

Think we can make it even better.

[WalkerWalker]

this problem still exists in version 0.12.0, postgres 9.6

log from exporter

ts=2023-03-27T15:32:00.188Z caller=collector.go:194 level=error msg="collector failed" name=replication_slot duration_seconds=0.011199623 err="pq: function pg_current_wal_lsn() does not exist"

log from postgres

postgres_1                        | ERROR:  function pg_current_wal_lsn() does not exist at character 25
postgres_1                        | HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
postgres_1                        | STATEMENT:  SELECT
postgres_1                        | 				slot_name,
postgres_1                        | 				pg_current_wal_lsn() - '0/0' AS current_wal_lsn,
postgres_1                        | 				coalesce(confirmed_flush_lsn, '0/0') - '0/0',
postgres_1                        | 				active
postgres_1                        | 			FROM
postgres_1                        | 				pg_replication_slots;

in version 0.10.0, the problem does not appear.

[rEVOLution-BlackMamba]

this problem still exists in version 0.12.0, postgres 9.6

log from exporter

ts=2023-03-27T15:32:00.188Z caller=collector.go:194 level=error msg="collector failed" name=replication_slot duration_seconds=0.011199623 err="pq: function pg_current_wal_lsn() does not exist"

log from postgres

postgres_1                        | ERROR:  function pg_current_wal_lsn() does not exist at character 25
postgres_1                        | HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
postgres_1                        | STATEMENT:  SELECT
postgres_1                        | 				slot_name,
postgres_1                        | 				pg_current_wal_lsn() - '0/0' AS current_wal_lsn,
postgres_1                        | 				coalesce(confirmed_flush_lsn, '0/0') - '0/0',
postgres_1                        | 				active
postgres_1                        | 			FROM
postgres_1                        | 				pg_replication_slots;

in version 0.10.0, the problem does not appear.

Same problem over here. Same version of postgres and postgres-exporter.

[halvaborsch]

you're mistaken, this is a new code that appeared in version 0.12.0

[halvaborsch]

https://github.com/prometheus-community/postgres_exporter/pull/747/files

version <10 were forgotten... again :(

[sysadmind]

It would appear that support for postgres < 10 was missed in that PR. Postgres 9 is not a version that we test against in our CI system. It's end of support so we don't go out of our way to test on it, but we don't intentionally break compatibility when we can avoid it. I think we could add that second query back to support < 10, but it would require some work to architect a maintainable system to handle different queries for different server versions.