Aufruf einer Prozedur durch versch. Trigger in einer Transaktion

Hallo zusammen,
meine Idee war, über eine zentrale Prozedur, die als Parameter NEW und OLD übergeben bekommt, zu prüfen bei welchen Spalten sich der Inhalt geändert hat und dies Änderung dann in einer Tabelle zu dokumentieren.
Das funktioniert so weit sehr gut. Allerdings gibt es ein Problem, wenn diese Prozedur innerhalb einer Transaktion* von Triggern (BEFORE UPDATE) verschiedener Tabellen aufgerufen wird.
Es kommt dann folgende Fehlermeldung:

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: type of parameter 1 (tabelle_2) does not match that when preparing the plan (tabelle_1)
CONTEXT: PL/pgSQL function own_log_aenderungen(record,record,text,text) line 12 at assignment
SQL statement “CALL cat_data.own_log_aenderungen(NEW, OLD, ‘tabelle_2’, NEW.primary_key::text)”
PL/pgSQL function trigger_fct_update_anweisungen() line 7 at CALL

Wenn ich die Prozedur “own_log_aenderungen” einfach noch einmal unter “own_log_aenderungen_2” abspeichere und entsprechen in den Triggern aufrufe ( so mein aktueller work around) klappt wieder alles wie es soll?

Ganz offensichtlich hat das etwas mit dem Ausführplan zu tun, den sich Postgres zurecht legt. Kann ich das irgendwie beinflussen? Ich konnte nichts dazu finden.

Hat dazu jemand eine Idee? Wenn noch Infos fehlen, dann einfach Bescheid geben.


*Die Datenbankeinträge erfolgen über php und PDO. Die UPDATE Anweisungen für die Tabellen (tabelle_1 und tabelle_2) sind eingebettet zwischen ->beginTransaction() und ->commit()

Wird schwierig ohne irgendwelchen Code zu sehen.

Anstatt das mit anonymen “records” zu machen, würde ich vermutlich NEW und OLD in jsonb Objekte umwandeln und die dann vergleichen (oder speichern). Das dürfte am einfachsten sein, wenn man das generisch machen will. Im Netz gibt es auch ziemlich viele Beispiele dazu. Einfach mal nach “postgresql audit trigger jsonb” suchen

Ich schreibe bei einer ähnlichen Anforderung einfach das new und das old in 2 JSONB-Spalten einer Logtabelle und kann die dann vergleichen.

Zunächst danke für Eure Antworten und Hinweise. Ich fürchte nur, das Problem ist damit nicht getroffen. Evtl. bin ich hier in der falschen Rubrik.
Ich versuche es noch einmal anders, vielleicht kann ich so mein Problem deutlicher machen.

Als erstes, der Vollständigkeit halber, die Prozedur, die wie oben schon gesagt vom Prinzip her auch funktioniert und es geht auch nicht darum, ob man das u.U. geschickter machen kann, es sei denn hier liegt die Ursache für die am Ende genannte Fehlermeldung.

CREATE OR REPLACE PROCEDURE own_log_aenderungen(newData record, oldData record, tableName text, primaryKey text)
    LANGUAGE 'plpgsql'
AS
$$
DECLARE
    newjson     JSON;
    oldjson     JSON;
    idx         TEXT;
    alterwert   TEXT;
    neuerwert   TEXT;
BEGIN

    newJson := to_jsonb(newData);
    oldJson := to_jsonb(oldData);

    FOR idx, neuerWert IN SELECT * FROM json_each(newjson)
        LOOP
            alterwert := json_extract_path(oldJson, idx);

            IF (alterWert != neuerWert)
            THEN
                INSERT into log_tabelle
                (tabellen_name, primary_key, feldname, alter_wert, neuer_wert, datum, name)
                VALUES
                (tablename, primaryKey, idx, alterWert, neuerWert, LOCALTIMESTAMP, newData.letzte_aenderung_name);
            END IF;
        END LOOP;
END;
$$;

Das zentrale Probelm ist, so weit ich es erkennen kann, der Ablauf des Ganzen:

UPDATE	tabelle_1
SET	    spalte_a =  'igendwas',
WHERE	key_tabelle_1 = 1111;


UPDATE tabelle_2
SET	    spalte_b = 'was anders,
WHERE	key_tabell_2 = 2222;

Für beide Tabellen gibt es einen BEFORE UPDATE Trigger, der jeweils eine spezifische Triggerfunktion aufruft:

CREATE FUNCTION trigger_fct_update_tabelle_1() RETURNS trigger
    LANGUAGE plpgsql
AS
$$
DECLARE

BEGIN
    CALL own_log_aenderungen(NEW, OLD, 'tabelle_1', NEW.key_tabelle_1);
    RETURN NEW;
END
$$;
CREATE FUNCTION trigger_fct_update_tabelle_2() RETURNS trigger
    LANGUAGE plpgsql
AS
$$
DECLARE

BEGIN
    CALL own_log_aenderungen(NEW, OLD, 'tabelle_2', NEW.key_tabelle_2);
    RETURN NEW;
END
$$;

Wenn ich jetzt die beiden Updates oben “in einem Rutsch” durchführen möchte dann bekomme ich eben diese Fehlermeldung:

ERROR:  type of parameter 1 (tabelle_2) does not match that when preparing the plan (tabelle_1)
CONTEXT:  PL/pgSQL function own_log_aenderungen(record,record,text,text) line *xx* at assignment
SQL statement "CALL cat_data.own_log_aenderungen_verwaltung(NEW, OLD, 'tabelle_2', NEW.key_tabelle_2)"
PL/pgSQL function trigger_fct_update_tabelle_2() line *xx* at CALL
SQL state: 42804

Bei meiner Frage geht es in erster Linie um diese Fehlermeldung, deren tiefer Bedeutung und Ursache mir verschlossen bleibt. Wenn dazu irgendwer eine Idee hätte wäre das prima.

Hmm, da kommen wohl irgendwie ge-cachte Ausführungspläne zum Einsatz. Irgendwie kann man das verhindern, aber auf die Schnelle finde ich das grad nicht mehr.

Aber Unabhängig davon: Deine Prozedur kann man erheblich vereinfachen. Du musst nicht ständig zwischen JSONB und JSON hin-und-her wechseln. Und die Schleife ist auch nicht notwendig, weil Du mit jsonb_each_text direkt über die Einträge iterieren kannst und das gleich für beide “records” wenn Du über die Spaltennamen einen join machst:

CREATE OR REPLACE PROCEDURE own_log_aenderungen(newData record, oldData record, tableName text, primaryKey text)
    LANGUAGE plpgsql
AS
$$
BEGIN

  INSERT into log_tabelle(tabellen_name, primary_key, feldname, alter_wert, neuer_wert, datum, name)
  select tablename, primaryKey, alt.col, alt.value, neu.value, LOCALTIMESTAMP, newData.letzte_aenderung_name
  from jsonb_each_text(to_jsonb(olddata)) as alt(col, value)
     join jsonb_each_text(to_jsonb(newdata)) as neu(col, value) 
       on alt.col = neu.col
  where alt.value is distinct from neu.value;

END;
$$;

Der Name der Sprache ist im Übrigen ein Identifier und sollte auf keinen Fall in einfachen Anführungsstrichen stehen. Die Unterstützung dafür wird in einer der nächsten Postgres Versionen entfernt

1 „Gefällt mir“

Ein Workaround bzgl. des Plan-Cache:

Mach nur eine Trigger-Funktion, und übergib den Wert des PK beim definieren des Trigger

create or replace function log_changes()
   returns trigger
as
$$
begin
    INSERT into log_tabelle (tabellen_name, primary_key, feldname, alter_wert, neuer_wert, datum, name)
    select TG_TABLE_NAME, TG_ARGV[0],   alt.col,  alt.value, neu.value, LOCALTIMESTAMP, new.letzte_aenderung_name
    from jsonb_each_text(to_jsonb(old)) as alt(col, value)
       join jsonb_each_text(to_jsonb(new)) as neu(col, value) 
         on alt.col = neu.col
    where alt.value is distinct from neu.value;
    return new;
end
$$
language plpgsql
;

Und dann in der Trigger-Definition übergibst Du den PK Wert der in der Trigger-Funktion über TG_ARGV[0] ausgelesen werden kann. Den Tabellennamen musst Du nicht übergeben, weil der in einer Triggerfunktion immer über TG_TABLE_NAME verfügbar ist (evtl. willst Du auch noch TG_TABLE_SCHEMA verwenden)

create trigger log_tabelle_2_aenderung_trg
  after update on tabelle_2
  for each row
  execute procedure log_changes(key_tabelle_2);
1 „Gefällt mir“

Vielen Dank für Deine schnellen Antworten.

Was die Prozedur “own_log_aenderungen” angeht habe ich schon befürchtet, dass es da elegantere Möglichkeiten gibt :wink:

Den zweiten Vorschlag werde ich gleich mal umsetzen. Nur noch eine Frage, Du verwendets hier den “after update”. Hat das einen tieferen Sinn? Ob nun “after” oder “before” schätze ich in diesem Fall als nicht entscheident ein. Oder irre ich da?


Der Hinweis auf die Anführungstriche bei der Sprache überrascht mich. Das wird so von pgAdmin 4 erzeugt, ich hab das nur kopiert. Aber ich weiß, dass es auch ohne Anführungsstriche funktioniert. Das aber nur am Rande.

Nein, Du irrst nicht. In diesem Fall ist es egal ob man before oder after verwendet.

Ich habe mir nur angewöhnt, dass Trigger die keine Änderungen machen “danach” ausgeführt werden. So kann man nicht versehentlich was kaputt machen.

Trigger die den Datensatz noch manipulieren möchten, müssen ja zwangsweise BEFORE sein.

Das würde ich als Bug in pgAdmin werten

Funktioniert alles wie es soll. Mit der Lösung kann ich sehr gut leben :smiley:
Ich habe auch den leisen Verdacht, dass es sich dabei gar nicht um einen Workaround handelt, sondern um die Lösung

In jedem Fall ist das Probelm für mich abgeschlossen und mir bleibt nur noch ein herzliches “Danke schön” für die Unterstützung
Schönes Wochenende