Aktuelle Session-PID bei Trigger-Event auslesen

Hallo liebes Forum

Ich habe eine Datenbank in PostgreSQL 9.3 auf die mehrere C#-Instanzen als Clients zugreifen. In meine Tabellen habe ich einen Trigger eingebaut, der sich meldet wenn bei Insert/Update/Delete die Tabelle geändert wurde. Jetzt sollen jeweils nur die Clients informiert werden über die Änderung die NICHT der Initiator der Änderung waren. Das Ganze habe ich bisher in C# kodiert gehabt und da hat es funktioniert. Nun wollte ich diese Überprüfung in die Datenbank verlagern und den Datenverkehr etwas eingrenzen, damit nicht der Initiator die Meldung für geänderten Tabellen enthält. Nun wollte ich fragen wie ich denn aus dem Trigger-Event die PID des Senders auslesen kann. In C# geht das über den payload. Beim Trigger direkt in der Datenbank müsste das doch über das new Feld gehen oder? Wobei mir das ja nur Informationen über die geänderte Tabelle gibt, nicht aber über den Initiator.

Wer kann mir da weiterhelfen?

new enthält nur die Spalten der Tabelle die geändert wird. Solange Du da keine PID drin hast, kannst Du auch nicht darauf zugreifen.

Wenn Du mit PID aber die Prozess-ID der Postgres Verbindung meinst, dann kannst Du die in SQL mittels der Funktion pg_backend_pid() auslesen: PostgreSQL: Documentation: 16: 9.26. System Information Functions and Operators

Ja das mit der pg_backend_pid() kenne ich, so habe ich das im Moment in C# gelöst. Ich hole mir für meine Verbindung meine backend_pid und vergleiche die mit der pid die im payload mitgeliefert wird. Sind die beiden pids gleich, war ich der Initiator und verwerfe die Meldung. Und das wollte ich eben innerhalb der Datenbank gleich lösen und nicht außerhalb. Daher meine Frage ob ich einem Triggerevent entnehmen kann von welcher pid der event initiiert wurde, wenn mehrere pids aktiv sind.

Das sollte pg_backend_pid() korrekt machen. Der Trigger läuft ja in dem Kontext der Transaktion und der Verbindung die den Trigger ausgelöst hat.

Hmm, o.k., ich versuche mich mal in einer möglichen Triggerfunktion:

CREATE OR REPLACE FUNCTION trigger_notification_message()
RETURNS trigger AS
$$
declare
backend_pid bigint;
session_pid integer[];
begin
SELECT pg_backend_pid() into backend_pid;
SELECT pid FROM pg_stat_activity into session_pid;
FOR i IN array_lower(session_pid,1)…array_upper(session_pid,1) LOOP
IF session_pid != backend_pid THEN
PERFORM pg_notify(‘transaction_watcher’,(‘Table ‘||TG_TABLE_NAME||’ was changed with operation ‘||TG_OP||’!’)::text);
END IF;
END LOOP;
RETURN NULL;
end
$$
LANGUAGE ‘plpgsql’;

Könnte das prinzipiell funktionieren? Oder ist die pg_stat_activity die falsche Anlaufstelle für den Vergleich mit den anderen (Client)pids? Wenn ja, wo finde ich die anderen PIDs der Clients? Oder muss ich einen ganz anderen Ansatz wählen um die Messages geziehlt nur an die Clients zu schicken die nicht Auslöser des Triggers waren?

Für normale Benutzer sind in der pg_stat_activity nur Verbindungen des gleichen User zu sehen. Wenn Du die Datenbank-Benutzer arthur und ford im System hat, und arthur den Trigger auslöst, wird diese Session niemals Einträge mit einem anderen Usernamen (Spalten usename) sehen.

Die Funktion pg_notify verschickt aber die Nachricht nicht an eine bestimmte PID sondern an alle Subscriber in dem Channel. Deine Schleife (die unnötig kompliziert ist im Übrigen), erreicht also genau das Gegenteil: Du verschickst die Nachricht mehrfach an den gleichen Channel, somit bekommt jede Session die bei dem Channel registriert ist die Nachricht so oft wie Sessions im System sind.

Übrigens: die Sprache einer Funktion ist ein SQL Identifier. Dieser sollte nicht in Anführungszeichen stehen: language plpgsql ist also die korrekte Schreibweise. Die single quotes werden derzeit noch aus Gründen der Rückwärtskompatibilität akzeptiert, das wird sich aber wohl in einem der nächsten Versionen ändern

Hmm, o.k., vielen Dank für die Antwort schonmal.

Aber gibt es dann überhaupt eine Möglichkeit den Auslöser des Triggers auszunehmen oder muss ich das einfach in Kauf nehmen dass ich als Auslöser die Triggermessage ebenfalls bekomme?

Ich glaube nicht.

Was ich mir vorstellen könnte, ist dass Du die PID des Backend mit in die Payload aufnimmst, und beim Empfänger dann darauf prüfst.

O.k., so hatte ich das bisher auch gelöst. Ich wollte nur sozusagen die überflüssigen Messages, die von mir ausgelöst wurden, gleich in der Datenbank aussortieren und nicht erst wenn ich sie als Client schon bekommen habe. Aber wenn das nicht geht dann bleibe ich eben bei meiner bisherigen Lösung. Funktioniert ja. Erzeugt halt nur mehr Datenverkehr.

Vielen Dank jedenfalls.