Conditional (predicate) queries - run a query to decide whether to run the main metric query

[ringerc]

It'd be helpful if sql_exporter had a means of making a metric's execution conditional on the outcome of a pre-check or predicate query that's run to decide whether this metric can be collected.

A means to override the metric group name used for output would also be needed, so that different db-server-version specific metric groups could be selected between, and emit the same metric names.

---

Sometimes different DB engine versions have different supported sets of tables and columns of interest. Extensions may add optional features too.

In such cases, it's desirable to be able to execute some metrics queries selectively based on inspection of the database.

For example, postgres 14 adds safe_wal_size and wal_status columns to pg_catalog.pg_replication_slots. An attempt to query this view for those columns will result in an ERROR on older postgres versions, but it's desirable to expose these.

Postgres unfortunately has no support for anonymous blocks that return rows. It has PL/PgSQL DO blocks but they can't return a resultset. Fetching results indirectly via a refcursor is possible but requires multi-statement queries, which sql_exporter doesn't support, and is clunky. It's also inefficient. So there's no in-query way to say "if postgres version >= 14, query for these cols, otherwise omit them".

I imagine this is done with (e.g.) T-SQL in MS-SQL. But even so, you'd have the problem of the metrics definition binding to columns that may or may not actually exist or have useful values. The code could emit placeholder values but doing so is producing useless metrics.

This means that whatever component deploys sql_exporter to monitor the database(s) would have to dynamically generate a configuration that is valid for the specific target postgres version, containing the correct queries and corresponding column bindings. Or at least, referencing a specific subset of config file snippets that's valid for this particular DB version.

And that falls down when considering extensions that can be optionally enabled or disabled within a database. There's no way to say "if the pg_stat_statements extension is installed in this database, then query the pg_stat_statements view for these metrics, otherwise skip this metric query because it would result in an error when executed". Users may also wish to be able to monitor application-specific and application-version-specific metrics.

So it would be helpful to be able to define a set of one or more queries that are run to decide whether the main query can be run on this DB. For example, this predicate query might be SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_extension WHERE extname = 'pg_stat_statements') or select current_setting('server_version_num')::integer >= 140000 or select true from information_schema.columns where table_schema = 'pg_catalog' and table_name = 'pg_replication_slots' and column_name = 'safe_wal_size'.

Any thoughts?