Create a way to configure metrics

[teoincontatto]

Provide a way to configure metrics. In particular:

• The frequency of metrics scraping and push to the open telemetry collector. For instance using a custom GUC like pgotel.metrics_scrape_frequency.
• The user that should be used to scrape and push the metrics. For instance using a custom GUC like pgotel.metrics_user.
• The query and the metrics type and labels obtained by each query. For instance creating a set of tables.

[teoincontatto]

Here is a proposal for the table structure that will contain the metrics. It is an adaptation to the postgres exporter's queries.yaml:

DROP SCHEMA IF EXISTS pgotel CASCADE;
CREATE SCHEMA pgotel;
CREATE TYPE pgotel_metric_type AS ENUM (
    'GAUGE',
    'MEASURE',
    'CUMULATIVE',
    'LABEL'
);

CREATE TABLE pgotel.metric_queries(id bigserial, name text, query text, master boolean, PRIMARY KEY (id), UNIQUE (name));
CREATE TABLE pgotel.metrics(id serial, query_id bigint REFERENCES pgotel.metric_queries(id), column_name text, type pgotel_metric_type, description text, PRIMARY KEY (id), UNIQUE (query_id, column_name));

INSERT INTO pgotel.metric_queries (name, query, master) VALUES (
'pg_blocked',
$sql$
    SET max_parallel_workers_per_gather = 0;
    WITH databases AS (
      SELECT oid, datname FROM pg_database
      WHERE datname NOT IN ('template0', 'template1')
    )
    SELECT
      locktype AS type,
      NULL AS datname,
      NULL AS schemaname,
      NULL AS reltype,
      NULL AS relname,
      count(*) AS queries
    FROM pg_catalog.pg_locks blocked
    WHERE NOT blocked.granted AND relation IS NULL
    GROUP BY locktype
    UNION
    SELECT
      locktype AS type,
      datname,
      schemaname,
      CASE relkind
        WHEN 'r' THEN 'ordinary table'
        WHEN 'i' THEN 'index'
        WHEN 'S' THEN 'sequence'
        WHEN 't' THEN 'TOAST table'
        WHEN 'v' THEN 'view'
        WHEN 'm' THEN 'materialized view'
        WHEN 'c' THEN 'composite type'
        WHEN 'f' THEN 'foreign table'
        WHEN 'p' THEN 'partitioned table'
        WHEN 'I' THEN 'partitioned index'
        ELSE 'unknown type ''' || relkind || ''''
        END AS reltype,
      relname,
      count(*) AS queries
    FROM pg_catalog.pg_locks blocked
    INNER JOIN databases
      ON blocked.database = databases.oid,
      LATERAL (SELECT * FROM dblink(
        'host=/var/run/postgresql port=5432 user=' || CURRENT_USER || ' sslmode=disable dbname=''' || regexp_replace(datname, '([.\\])', '\\\1', 'g') || '''',
        'SELECT nspname as schemaname, relkind, relname FROM pg_catalog.pg_class LEFT JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = relnamespace) WHERE pg_class.oid = ' || blocked.relation)
        AS (schemaname name, relkind char, relname name)) AS _
    WHERE NOT blocked.granted AND relation IS NOT NULL
    GROUP BY locktype, datname, schemaname, reltype, relname;
$sql$,
true);

INSERT INTO pgotel.metrics (query_id, column_name, type, description) VALUES (
(SELECT id FROM pgotel.metric_queries WHERE name = 'pg_blocked'),
'type',
'LABEL',
'The lock type'),
(
(SELECT id FROM pgotel.metric_queries WHERE name = 'pg_blocked'),
'datname',
'LABEL',
'Database name'),
(
(SELECT id FROM pgotel.metric_queries WHERE name = 'pg_blocked'),
'schemaname',
'LABEL',
'The schema on which a query is blocked'),
(
(SELECT id FROM pgotel.metric_queries WHERE name = 'pg_blocked'),
'reltype',
'LABEL',
'The type of relation'),
(
(SELECT id FROM pgotel.metric_queries WHERE name = 'pg_blocked'),
'relname',
'LABEL',
'The relation on which a query is blocked'),
(
(SELECT id FROM pgotel.metric_queries WHERE name = 'pg_blocked'),
'queries',
'GAUGE',
'The current number of blocked queries');