`allow_zero_rows` does not work

mfuhrmann commented 1 year ago

Regarding https://github.com/justwatchcom/sql_exporter/pull/39

allow_zero_rows true or false should help if a query does not get a row as result. I was using an old version 0.4.0 that didn't include the patch mentioned above. Now I am using

https://apt.postgresql.org/pub/repos/apt/pool/main/p/prometheus-sql-exporter/prometheus-sql-exporter_0.4.5-1.pgdg22.04+1_amd64.deb

But I still don't see any difference in the behavior.

The error is still the same: {"caller":"job.go:205","err":"zero rows returned","job":"lindeconnect_hr","level":"warn","msg":"Failed to run query","query":"corrupt_cus_portal","ts":"2022-11-21T14:36:25.857520723Z"} Doesn't matter if I set true or false. I would have expected that I get the metric scraped with a value of zero.

An example query what I've used looks like:

  - name: "corrupt_cus_portal"
    help: "corrupt portal cus"
    values:
      - "portal"
    query:  |
            SELECT
              portal
            FROM
              corrupt_cu_statistic ccs
            WHERE
              dc::date = now()::date limit 1;
    allow_zero_rows: true

How should that work?

marevers commented 1 year ago

As far as I know, that is not what this function does. The metric does not get scraped either way. It is to prevent errors being thrown if zero rows are returned.

By default, the exporter expects every metric to return a result. If zero rows are returned (and allow_zero_rows is set to false or not specified), an error will be thrown as the query is assumed to be faulty. If it is set to true, zero rows is assumed to be a valid response which it can be, depending on the query.

mfuhrmann commented 1 year ago

OK. Thanks for the feedback. Closing

justwatchcom / sql_exporter

`allow_zero_rows` does not work #75