influxdata / telegraf

Agent for collecting, processing, aggregating, and writing metrics, logs, and other arbitrary data.
https://influxdata.com/telegraf
MIT License
14.35k stars 5.54k forks source link

outputs/postgresql does not use uint extension if requested #15467

Closed alpex8 closed 1 month ago

alpex8 commented 1 month ago

Relevant telegraf.conf

[agent]
  debug=true

[[outputs.postgresql]]
  connection="postgres://$TS_USER:$TS_PASSWORD@$TS_HOST:$TS_PORT/$TS_DATABASE?sslmode=disable&pool_max_conns=5"
  uint64_type="uint8"
  tags_as_foreign_keys=true
  create_templates = [
    '''CREATE TABLE {{ .table }} ({{ .columns }})''',
    '''SELECT create_hypertable({{ .table|quoteLiteral }}, 'time', chunk_time_interval => INTERVAL '7d')''',
    '''ALTER TABLE {{ .table }} SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag_id')''',
  ]
  add_column_templates = [
    '''ALTER TABLE {{ .table }} ADD COLUMN IF NOT EXISTS {{ .columns|join ", ADD COLUMN IF NOT EXISTS " }}''',
    '''DROP VIEW IF EXISTS {{ .table.WithSchema "public" }}''',
    '''CREATE OR REPLACE VIEW {{ .table.WithSchema "public" }} AS SELECT time, {{ (.tagTable.Columns.Tags.Concat .allColumns.Fields).Identifiers | join "," }} FROM {{ .table }} t, {{ .tagTable }} tt WHERE t.tag_id = tt.tag_id''',
  ]
  tag_table_add_column_templates = [
    '''ALTER TABLE {{ .table }} ADD COLUMN IF NOT EXISTS {{ .columns|join ", ADD COLUMN IF NOT EXISTS "}}''',
    '''DROP VIEW IF EXISTS {{ .metricTable.WithSchema "public" }}''',
    '''CREATE OR REPLACE VIEW {{ .metricTable.WithSchema "public" }} AS SELECT time, {{ (.allColumns.Tags.Concat .metricTable.Columns.Fields).Identifiers | join "," }} FROM {{ .metricTable }} t, {{ .tagTable }} tt WHERE t.tag_id = tt.tag_id''',
  ]

[[inputs.cpu]]

Logs from Telegraf

2024-06-06T07:53:31Z I! Loading config: /etc/telegraf/telegraf.conf
2024-06-06T07:53:31Z I! Starting Telegraf 1.30.3 brought to you by InfluxData the makers of InfluxDB
2024-06-06T07:53:31Z I! Available plugins: 233 inputs, 9 aggregators, 31 processors, 24 parsers, 60 outputs, 6 secret-stores
2024-06-06T07:53:31Z I! Loaded inputs: cpu
2024-06-06T07:53:31Z I! Loaded aggregators: 
2024-06-06T07:53:31Z I! Loaded processors: 
2024-06-06T07:53:31Z I! Loaded secretstores: 
2024-06-06T07:53:31Z I! Loaded outputs: postgresql
2024-06-06T07:53:31Z I! Tags enabled: host=671e48cb99f6
2024-06-06T07:53:31Z I! [agent] Config: Interval:10s, Quiet:false, Hostname:"671e48cb99f6", Flush Interval:10s
2024-06-06T07:53:31Z D! [agent] Initializing plugins
2024-06-06T07:53:31Z D! [agent] Connecting outputs
2024-06-06T07:53:31Z D! [agent] Attempting connection to [outputs.postgresql]
2024-06-06T07:53:31Z D! [agent] Successfully connected to outputs.postgresql
2024-06-06T07:53:31Z D! [agent] Starting service inputs
2024-06-06T07:53:41Z D! [outputs.postgresql] Buffer fullness: 0 / 10000 metrics
2024-06-06T07:53:51Z D! [outputs.postgresql] cache: size=0 hit=0 miss=0 full=0
2024-06-06T07:53:51Z D! [outputs.postgresql] Wrote batch of 17 metrics in 167.606µs
2024-06-06T07:53:51Z D! [outputs.postgresql] Buffer fullness: 0 / 10000 metrics

System info

Telegraf 1.30.3, Docker version 26.1.3

Docker

No response

Steps to reproduce

  1. Create Files for Building Timescale DB Image with pguint extension, seeding and running:

Containerfile

FROM timescale/timescaledb:2.15.1-pg15

RUN apk add --no-cache git make build-base python3 clang15 llvm15
RUN git clone https://github.com/petere/pguint.git \
    && cd pguint \
    && git checkout 1.20231206 \
    && make \
    && make install \
    && cd / \
    && rm -rf pguint
RUN apk del --no-network llvm15 clang15 python3 build-base make git

db_seed.sh

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
        CREATE USER tsc WITH PASSWORD 'tsc';
        CREATE DATABASE timeseries OWNER tsc;
        GRANT ALL PRIVILEGES ON DATABASE timeseries TO tsc;
        \c timeseries;
        create extension uint;
EOSQL
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$TS_DATABASE" <<-EOSQL1
        create extension uint;
EOSQL1

docker-compose.yml

services:
  timescale:
    container_name: timescale.db
    hostname: timescale.db
    build:
      context: ./
      dockerfile: Containerfile
    environment:
      POSTGRES_PASSWORD: 1234567890
      TS_USER: tsc
      TS_PASSWORD: tsc
      TS_DATABASE: timeseries
    volumes:
      - ./db_seed.sh:/docker-entrypoint-initdb.d/100_db_seed.sh

  telegraf:
    container_name: telegraf
    image: telegraf:1.30-alpine
    depends_on:
      - timescale
    volumes:
      - ./telegraf.conf:/etc/telegraf/telegraf.conf
    environment:
      TS_USER: tsc
      TS_PASSWORD: tsc
      TS_HOST: timescale.db
      TS_PORT: 5432
      TS_DATABASE: timeseries

telegraf.conf

[agent]
  debug=true

[[outputs.postgresql]]
  connection="postgres://$TS_USER:$TS_PASSWORD@$TS_HOST:$TS_PORT/$TS_DATABASE?sslmode=disable&pool_max_conns=5"
  uint64_type="uint8"
  tags_as_foreign_keys=true
  create_templates = [
    '''CREATE TABLE {{ .table }} ({{ .columns }})''',
    '''SELECT create_hypertable({{ .table|quoteLiteral }}, 'time', chunk_time_interval => INTERVAL '7d')''',
    '''ALTER TABLE {{ .table }} SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag_id')''',
  ]
  add_column_templates = [
    '''ALTER TABLE {{ .table }} ADD COLUMN IF NOT EXISTS {{ .columns|join ", ADD COLUMN IF NOT EXISTS " }}''',
    '''DROP VIEW IF EXISTS {{ .table.WithSchema "public" }}''',
    '''CREATE OR REPLACE VIEW {{ .table.WithSchema "public" }} AS SELECT time, {{ (.tagTable.Columns.Tags.Concat .allColumns.Fields).Identifiers | join "," }} FROM {{ .table }} t, {{ .tagTable }} tt WHERE t.tag_id = tt.tag_id''',
  ]
  tag_table_add_column_templates = [
    '''ALTER TABLE {{ .table }} ADD COLUMN IF NOT EXISTS {{ .columns|join ", ADD COLUMN IF NOT EXISTS "}}''',
    '''DROP VIEW IF EXISTS {{ .metricTable.WithSchema "public" }}''',
    '''CREATE OR REPLACE VIEW {{ .metricTable.WithSchema "public" }} AS SELECT time, {{ (.allColumns.Tags.Concat .metricTable.Columns.Fields).Identifiers | join "," }} FROM {{ .metricTable }} t, {{ .tagTable }} tt WHERE t.tag_id = tt.tag_id''',
  ]

[[inputs.cpu]]
  1. docker-compose build
  2. docker-compose up -d timescale # avoid errors in telegraf logs during DB startup time
  3. docker-compose up -d
  4. docker exec -it timescale bash
  5. psql -U postgres -d timeseries
  6. \d cpu

Expected behavior

tag_id column has type uint8.

timeseries=# \d cpu
                               Table "public.cpu"
      Column      |            Type             | Collation | Nullable | Default 
------------------+-----------------------------+-----------+----------+---------
 time             | timestamp without time zone |           | not null | 
 tag_id           | uint8  <-----               |           |          |
 usage_guest      | double precision            |           |          | 
 usage_guest_nice | double precision            |           |          | 

Actual behavior

tag_id column has type bigint.

timeseries=# \d cpu
                               Table "public.cpu"
      Column      |            Type             | Collation | Nullable | Default 
------------------+-----------------------------+-----------+----------+---------
 time             | timestamp without time zone |           | not null | 
 tag_id           | bigint  <-----              |           |          | 
 usage_guest      | double precision            |           |          | 
 usage_guest_nice | double precision            |           |          | 

Additional info

Verify that extension uint is installed properly.

\dx
                                                List of installed extensions
    Name     | Version |   Schema   |                                      Description                                      
-------------+---------+------------+---------------------------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.15.1  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
 uint        | 0       | public     | unsigned integer types
powersj commented 1 month ago

Hi,

The uint support is only used if the metric value that is read in is of the uint64 type. See here:

https://github.com/influxdata/telegraf/blob/45e9ae4658fd4f234f4f90ad26abc193825aee26/plugins/outputs/postgresql/datatypes.go#L31-L36

I'm not sure where the value of tag_id comes from, but the values from the CPU plugin are not uint64, so there is no need to return a uint64.

alpex8 commented 1 month ago

Ah I see, thanks for the explanation.

I might have gotten a bit confused. In the configuration I attached the tags are saved in a separate table and the relation to that tag-table is given via the values in the tag_id column. However I have seen negative values in that tag_id column.

So because I enabled the property for using the uint types, I thought there is a bug in the table setup and the written values have suffered from an overflow.

Also with the given config (pretty much from the examples given in the plugin README.d) there isn´t any foreign key constraint between field and tag table, but I´m not sure whether this might be intentional for better performance.

So if you are still confident the actual behavior is as expected than feel free to close this issue. :)

powersj commented 1 month ago

Thanks for following up.

the tags are saved in a separate table and the relation to that tag-table is given via the values in the tag_id column

Ah thanks for clarifying. Yeah I would only expect the uint8 support to be on actual data you are writing from Telegraf.

So if you are still confident the actual behavior is as expected than feel free to close this issue. :)

will do.