Schedule policy support to run collector on specific database

[Vonng]

Can be used for application level metrics collector.

For example.

-- get realtime biz user count
SELECT count(*) FROM users;
SELECT reltuples FROM pg_class WHERE relname = 'users'

[mjf]

@Vonng Does this mean you aim to implement some sort of periodic scheduler (eg. Cron-like)? If so, it would be nice if the implementation also supported @daily, @hourly, @weekly, @montly, @annually aliases or (perhaps better) provide a general configuration for the aliases:

---
scheduler:
    aliases:

        # The following "schedule" values would be hardcoded and could be used in aliases too:
        # after-start, before-reload, after-reload, before-stop
        # meaning to run once after or before the specific daemon event...

        # Standard Cron aliases (could be also hard-coded)

        \@reboot: after-start

        \@yearly:   '0 0 1 1 *'
        \@annually: '0 0 1 1 *'
        \@monthly:  '0 0 1 * *'
        \@weekly:   '0 0 * * 0'
        \@daily:    '0 0 * * *'
        \@hourly:   '0 * * * *'

        # Custom aliases:

        \@shutdown: before-stop

        # The "@" is not neccessary, it's just name of an registered valid alias...

        every-3h:         '0 */3 * * *'
        workhours-hourly: '0 *   * * 1-5'
        sunday-midnight:  '0 0   * * 0'

Idea: if you prepended another collumn, say for seconds and milliseconds (!), you could also achieve:

---
scheduler:
    aliases:
        # alias  ms    sec  min hr dom mon dow
        100ms:  '*/100 *    *   *  *   *   *'
        10s:    '0     */10 *   *  *   *   *'

Also, when scheduling, for every task there should be a way to add some randomization around the precise schedule time for tasks to get spread a little (just like Cron allows to).

---

Additional ideas:

I think there should be an option to tweak the schedulers to support different "time counting" models: closest-boundary-based (or perhaps we could call it clock-based, see later), daemon-start-based and, of course unix-epoch-based scheduling. The Cron-like scheduler discussed here is clearly to be based on Unix epoch, it is the "precise" one (if no randomization of scheduled point in time was configured).

But any regular interval scheduling SHOULD distinquish whether a user wants to eg. run job at closest boundary of the "parent time frame" (which is minute for seconds, hour for minutes, day for hours). So, for example, for jobs expected to run every 3600 seconds (1 hour) the point in time the job would run is at next hour start and not every hour from daemon start! Users should be able to influence this.

The remaining "interval-based" scheduling "model" is common for almost every probe I have ever seen and is utterly wrong in some cases! The software just starts jobs counting the time from its start. Well, why not, right? If you collect something every single second, it makes not much difference. But for, say, one-hour-interval job that you know will run 10 minutes it's worth considering... You may want to be sure of the fact that it will always start at HH:00:00...

And the last idea to consider: what about time zones? The default should be UTC, that's clear-enough, but what if I wanted to synchronize with day/night in Singapore and N.Y. city? Again, no probe provides such support AFAIK.

[Vonng]

Cron would be too complicated for a prometheus exporter , Since the scrape policy is control by promethues side.

The initial idea was collect some app level metrics from database directly (e.g active user name, new user name) (per scrape interval)

collectors have two typical categories:

• Run once per postgres server cluster
• Run once upon each postgres database

There should be another policy:

• Run once on specific business database

But this can be implemented by tags dbname:xxx, so ...

[ringerc]

This can also be done by using a predicate query to check information_schema or the like for the presence of business-database-specific objects.

That way you can express things like "query some info from this extension if it is installed in this db" and aren't restricted to name-pattern matching only.