Trigger bei Insert der einen Wert berechnet

Hallo Zusammen,

Ich nutze für meine Heimautomatisierung den IOBroker. Für die Historisierung von Status-Werten verwendet ich Postgresql. Für die Visualisierung Grafana. Im Zusammenspiel Grafana / iobroker gibt es das Problem, dass iobroker die timestamps in ms speichert, grafana aber nur Sekunden oder Nanosekunden kennt. Ich habe das bis jetzt so gelöst, dass ich den timestamp durch 1000 teile um auf Sekunden zu kommen. Die Performance ist aber saumäßig: statt 60 ms dauert eine einfache query 8 Sekunden. Die würde ich gerne verbessern und denke da an einen Insert Trigger: Immer wenn ein neuer Wert geschrieben wird, soll der ms Wert in Sekunden umgerechnet und in ein zus. Attribut gespeichert werden.

Die Tabelle ist wie folgt aufgebaut:

ts_number       (Tabelle)
   ts                 (timestamp der Änderung in ms (bigint)
   val               (der wert des Objektes)
   id                (ID des Objektes
   ts_sec           (zu berechnender timestamp in sekunden (bigint)

Habt Ihr mir dazu einen Tip oder am besten gleich den SQL Code :slight_smile: ?
Falls Sekunden nicht gehen (es könnten Kommawerte entstehen) geht für mich auch Umrechnung in Nanosekunden.

Vielen Dank

timestamp der Änderung in ms (bigint)

Ganz schlechte Idee. Ein “timestamp” sollte in einer Spalte mit dem Datentyp timestamp gespeichert sein. (Siehe z.B. hier)

Die Spalte ts_sec kann man leicht als “generated column” definieren:

create table ts_number 
(
  ts bigint,
  val int,
  id int, 
  ts_sec bigint generated always as (ts/1000) stored
);

Ich würde es aber so machen:

create table ts_number 
(
  ts timestamp,
  val int,
  id int, 
  ts_sec bigint generated always as (extract(epoch from ts)) stored
);
1 „Gefällt mir“

Vielen Dank für die schnelle Antwort. Die erste Variante hat grundsätzlich funktioniert. Leider war die Performance auch sehr schlecht, praktisch keine Verbesserung.

Die 2te hat nicht getan. Ich bekomme:

ERROR: FEHLER:  Funktion pg_catalog.extract(unknown, bigint) existiert nicht
HINT:  Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.


SQL state: 42883

Anbei auch noch, falls es hilft, die Queries. Die obere mit der generated column läuft ca. 7 Sek, die untere auf der original spalte nur 50 ms. Indexes sind gesetzt auf id, ts und ts_sec

SELECT
  ts_sec AS "time",
  val AS "Verbrauch"
FROM ts_number
WHERE
  ts_sec >= 1666715215 and ts_sec <= 1666726015 and id = 4

ORDER BY 1 DESC;

SELECT
  ts AS "time",
  val AS "Verbrauch"
FROM ts_number
WHERE
  ts >= 1666715215000 and ts <= 1666726015000 and id = 4

ORDER BY 1 ASC

Hast du noch andere Ideen? Sonst wie würde es mit dem Trigger aussehen?

Funktioniert einwandfrei - hast Du dabei wirklich die Spalte ts als timestamp definiert?

Hast Du einen Index auf alle drei Spalten? Du brauchst entweder einen auf (id, ts) oder einen auf (id, ts_sec)

Zeig uns doch mal den Execution plan: explain (analzye, buffers, format text) select ...

Das mit dem timestamp habe ich tatsächlich übersehen. Aber unabhängig davon wollte ich die spalten ts, id, und val nicht anpassen, weil die vom IOBroker kommen und ich nicht weiß, was das für Auswirkungen hat.

ABER: es funktioniert jetzt dennoch wie erwartet. Ich glaube der Tip mit dem vielen Indexes war’s Ich hab den Index auf ts entfernt und jetzt läuft alles wie erwartet und hoch performant.

Vielen Dank :slight_smile: