View pg_stat_statements broken in pg13

[parisni]

several fields have been renamed in pg 13 (total_time -> total_exec_time). Then the below query is changed to make the queries.yaml working

  query: "SELECT t2.rolname, t3.datname, queryid, calls, total_exec_time / 1000 as total_time_seconds, min_exec_time / 1000 as min_time_seconds, max_exec_time / 1000 as max_time_seconds, mean_exec_time / 1000 as mean_time_seconds, stddev_exec_time / 1000 as stddev_time_seconds, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, loc
al_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds FROM pg_stat_statements t1 JOIN 
pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'"```

[SuperQ]

Want to open a PR to update it?

[parisni]

not sure, because the query won't work on pg < 13 . A PR means introduce a dedicated yaml file ?

[cobolbaby]

parseUserQueries should support runonserver options in *.yml

postgres_exporter/cmd/postgres_exporter/postgres_exporter.go

Lines 121 to 127 in c51a28f

	type UserQuery struct {
	Query string `yaml:"query"`
	Metrics []Mapping `yaml:"metrics"`
	Master bool `yaml:"master"` // Querying only for master database
	CacheSeconds uint64 `yaml:"cache_seconds"` // Number of seconds to cache the namespace result metrics for.
	RunOnServer string `yaml:"runonserver"` // Querying to run on which server version
	}

postgres_exporter/cmd/postgres_exporter/postgres_exporter.go

Lines 520 to 535 in c51a28f

	func parseUserQueries(content []byte) (map[string]intermediateMetricMap, map[string]string, error) {
	var userQueries UserQueries
	err := yaml.Unmarshal(content, &userQueries)
	if err != nil {
	return nil, nil, err
	}
	// Stores the loaded map representation
	metricMaps := make(map[string]intermediateMetricMap)
	newQueryOverrides := make(map[string]string)
	for metric, specs := range userQueries {
	level.Debug(logger).Log("msg", "New user metric namespace from YAML metric", "metric", metric, "cache_seconds", specs.CacheSeconds)
	newQueryOverrides[metric] = specs.Query
	metricMap, ok := metricMaps[metric]

[mr-tron]

Does anybody know how it can be fixed without kludges via custom queries.yml?

[vl4deee11]

Does anybody know how it can be fixed without kludges via custom queries.yml?

Added fix for version >= 13.0.0

[veera-chenna]

Facing the same issue. This PR-553 is awsome. Can someone review the PR and release it as a new feature?

[rasta-rocket]

Yep same here ^^

[Timusan]

Running into this as well. PG 14 has been released for a while now, this exporter breaks there to. Someone might want to review PR-553?

[combrs]

Also encounter this.
I see some conflict in warning about deprecate custom query.yml
`Please note, the following features are deprecated and may be removed in a future release: extend.query-path "
And not fixed stat_statements collector , see line 78 in collectors/pg_stat_statements.go in master branch
And this bug is open, but in helmchart repo there are fixed queries, but in queries file
Before deprecate queries file someone need to fix collector..

[SuperQ]

We do not control the helm charts. Those are up to whoever maintains those.

Since we now have version information in the collector package, it should be easy enough to make the query dynamic.

[combrs]

build container from current master after PR merge
pg ver 13.3

postgres_exporter, version 0.13.1 (branch: master, revision: f9277b04b7f614a03a4ca70194abe2045f17c36a)
ts=2023-07-25T14:44:47.786Z caller=postgres_exporter.go:613 level=info msg="Semantic version changed" server=mypgdb:5432 from=0.0.0 to=13.3.0
ts=2023-07-25T14:44:47.761Z caller=collector.go:190 level=error msg="collector failed" name=stat_statements duration_seconds=0.183012101 err="pq: column \"total_time\" not exists"

Maybe it is because line 110 WITHIN GROUP (ORDER BY total_time)

[SuperQ]

Ahh, sorry, I missed that change.

[combrs]

Works as expected now

postgres_exporter, version 0.13.1 (branch: master, revision: 716ac23f202f664845d04c0d7b07000feae19c59)
ts=2023-07-26T12:33:49.283Z caller=collector.go:194 level=debug msg="collector succeeded" name=stat_statements duration_seconds=0.491161838
ts=2023-07-26T12:33:49.351Z caller=postgres_exporter.go:613 level=info msg="Semantic version changed" server=mypgdb:5432 from=0.0.0 to=13.3.0

[roock]

@SuperQ is there an ETA for a new version which fixes this issue?