ProHoster > Blog > Administration > Studie zur Implementierung von GeschÀftslogik auf der Ebene von PostgreSQL-Stored Functions

Studie zur Implementierung von GeschÀftslogik auf der Ebene von PostgreSQL-Stored Functions

Der Anstoß fĂŒr das Schreiben dieser Studie war ein Artikel „ WĂ€hrend der QuarantĂ€ne ist die Last um das FĂŒnffache gestiegen, aber wir waren bereit“. Wie Lingualeo mit 23 Millionen Nutzern auf PostgreSQL umgestiegen ist. Ebenso erschien mir ein vier Jahre alter Artikel interessant — Implementierung von GeschĂ€ftslogik in MySQL.

Es fiel auf, dass derselbe Gedanke —die GeschĂ€ftslogik in der Datenbank zu implementieren".

Studie zur Implementierung von GeschÀftslogik auf der Ebene von PostgreSQL-Stored Functions

nicht nur mir allein gekommen ist.

FĂŒr die Zukunft wollte ich auch interessante Ergebnisse, die wĂ€hrend der Umsetzung entstanden sind, fĂŒr mich persönlich festhalten. Besonders da vor kurzem eine strategische Entscheidung fĂŒr den Wechsel der Architektur und die Verlagerung der GeschĂ€ftslogik auf die Backend-Ebene getroffen wurde. Alles, was erarbeitet wurde, wird schnell niemandem mehr nĂŒtzlich sein und niemanden interessieren.

Die beschriebenen Methoden sind kein besonderes Geheimnis oder außergewöhnliches Know-how, alles klassisch und wurde bereits mehrfach umgesetzt (ich habe diesen Ansatz zum Beispiel vor 20 Jahren bei Oracle angewendet). Ich habe einfach beschlossen, alles an einem Ort zusammenzufassen. Vielleicht kommt es jemandem zugute. Wie die Praxis zeigt, haben oft verschiedene Menschen unabhĂ€ngig voneinander die gleiche Idee. Und es ist auch gut, es fĂŒr sich selbst als Erinnerung zu behalten.

NatĂŒrlich ist nichts in dieser Welt perfekt, und Fehler sowie Tippfehler sind leider möglich. Kritik und Anmerkungen sind jederzeit willkommen und erwartet. Ein weiteres kleines Detail — spezifische Implementierungsdetails wurden weggelassen. Es wird schließlich noch in einem tatsĂ€chlich laufenden Projekt verwendet. Daher ist der Artikel eher ein EtĂŒde und eine Beschreibung des allgemeinen Konzepts, nicht mehr. Ich hoffe, dass die Details ausreichen, um ein Gesamtbild zu verstehen.

Die allgemeine Idee — „teilen und herrschen, verbergen und besitzen“

Die Idee ist klassisch — ein eigenes Schema fĂŒr Tabellen, ein eigenes Schema fĂŒr gespeicherte Funktionen.
Der Kunde hat keinen direkten Zugriff auf die Daten. Alles, was der Kunde tun kann, ist, eine gespeicherte Funktion aufzurufen und die erhaltene Antwort zu verarbeiten.

Rollen

CREATE ROLE store;


CREATE ROLE sys_functions;


CREATE ROLE loc_audit_functions;


CREATE ROLE service_functions;


CREATE ROLE business_functions;

Schemata

Schemas fĂŒr die Speicherung von Tabellen

Zieltabellen, die fachliche EntitÀten implementieren.

CREATE SCHEMA store AUTHORIZATION store ;

Schema der Systemfunktionen

Systemfunktionen, insbesondere fĂŒr die Protokollierung von TabellenĂ€nderungen.

CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;

Schema der lokalen Audits

Funktionen und Tabellen zur Implementierung des lokalen Audits fĂŒr die AusfĂŒhrung gespeicherter Funktionen und Änderungen an Zieltabellen.

CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;

Schema der Dienstfunktionen

Funktionen fĂŒr Service- und DML-Funktionen.

CREATE SCHEMA service_functions AUTHORIZATION service_functions;

Schema der Business-Funktionen

Funktionen fĂŒr die Endbusiness-Funktionen, die vom Client aufgerufen werden.

CREATE SCHEMA business_functions AUTHORIZATION business_functions;

Zugriffsrechte

Rolle — DBA hat vollstĂ€ndigen Zugriff auf alle Schemata (getrennt von der Rolle DB Owner).

CREATE ROLE dba_role;
GRANT store TO dba_role;
GRANT sys_functions TO dba_role;
GRANT loc_audit_functions TO dba_role;
GRANT service_functions TO dba_role;
GRANT business_functions TO dba_role;

Rolle — USER hat die Berechtigung EXECUTE im Schema business_functions.

CREATE ROLE user_role;

Berechtigungen zwischen den Schemata

GRANT
Da alle Funktionen mit dem Attribut SECURITY DEFINER eine Anweisung erforderlich ist REVOKE EXECUTE ON ALL FUNCTION
 FROM public;

ENTZIEHEN Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA sys_functions VON public; 
ENTZIEHEN Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA loc_audit_functions VON public; 
ENTZIEHEN Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA service_functions VON public; 
ENTZIEHEN Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA business_functions VON public; 

GewÀhren Sie die Nutzung DES SCHEMAS sys_functions an dba_role; 
GewĂ€hren Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA sys_functions AN dba_role; 
GewÀhren Sie die Nutzung DES SCHEMAS loc_audit_functions AN dba_role; 
GewĂ€hren Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA loc_audit_functions AN dba_role; 
GewÀhren Sie die Nutzung DES SCHEMAS service_functions AN dba_role; 
GewĂ€hren Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA service_functions AN dba_role; 
GewÀhren Sie die Nutzung DES SCHEMAS business_functions AN dba_role; 
GewĂ€hren Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA business_functions AN dba_role; 
GewĂ€hren Sie EXECUTE RECHTE FÜR ALLE FUNKTIONEN IM SCHEMA business_functions AN user_role; 

GewĂ€hren Sie ALLE RECHTE FÜR DAS SCHEMA store AN DIE GRUPPE business_functions; 
GewĂ€hren Sie ALLE RECHTE FÜR ALLE TABELLEN IM SCHEMA store AN business_functions; 
GewÀhren Sie die Nutzung ALLER SEQUENZEN IM SCHEMA store AN business_functions;

Die Datenbankschema ist nun bereit. Sie können mit der BefĂŒllung der Daten beginnen.

Zieltabellen

Die Erstellung der Tabellen ist unkompliziert. Es gibt keine besonderen Merkmale, außer dass entschieden wurde, auf die Verwendung von SERIAL zu verzichten und die Sequenzen explizit zu generieren. Zudem ist die maximale Nutzung der Anweisung

COMMENT ON ...

Kommentare fĂŒr alle Objekte, ohne Ausnahmen.

Lokaler Audit

Zur Protokollierung der AusfĂŒhrung gespeicherter Funktionen und der Änderungen an Zieltabellen wird eine lokale Audittabelle verwendet, die unter anderem Details zur Kundenverbindung, das Label des aufgerufenen Moduls sowie die tatsĂ€chlichen Werte der Eingangs- und Ausgangsparameter im JSON-Format umfasst.

Systemfunktionen

Dienen der Protokollierung von Änderungen an Zieltabellen. Handeln sich um Triggerfunktionen.

Vorlage — Systemfunktion

---------------------------------------------------------
-- EINFÜGEN
CREATE OR REPLACE FUNCTION sys_functions.table_insert_log ()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM loc_audit_functions.make_log( ' '||'table' , 'einfĂŒgen' , json_build_object('id', NEW.id)  );
  RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER table_after_insert AFTER INSERT ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_insert_log();

---------------------------------------------------------
-- UPDATE
CREATE OR REPLACE FUNCTION sys_functions.table_update_log ()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.column != NEW.column
  THEN
    PERFORM loc_audit_functions.make_log( ' '||'table' , 'aktualisieren' , json_build_object('OLD.column', OLD.column , 'NEW.column' , NEW.column )  );
  END IF ;
  RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER table_after_update AFTER UPDATE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_update_log ();

---------------------------------------------------------
-- LÖSCHEN
CREATE OR REPLACE FUNCTION sys_functions.table_delete_log ()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM loc_audit_functions.make_log( ' '||'table' , 'löschen' , json_build_object('id', OLD.id )  );
  RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER table_after_delete AFTER DELETE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_delete_log ();

Dienstfunktionen

Dienen der DurchfĂŒhrung von Dienst- und DML-Operationen auf den Zieltabellen.

Vorlage — Dienstfunktion

--EINFÜGEN
--GIB ID DER NEUEN ZEILE ZURÜCK
ERSTELLE ODER ERSETZE FUNKTION service_functions.table_insert ( neue_spalte store.table.column%TYPE )
GIBT ganzzahl AS $$
DEKLARIERE
  neue_id ganzzahl ;
BEGIN
  -- Generiere neue ID
  neue_id = nextval('store.table.seq');

  -- EinfĂŒgen in die Tabelle
  FÜGE HINZU store.table
  ( 
    id ,
    spalte
   )
  WERTE
  (
   neue_id ,
   neue_spalte
   );

RÜCKGABE neue_id ;
END
$$ SPRACHE plpgsql SICHERHEIT DEFINIERER;

--LÖSCHEN
--GIB REIHENNUMMER DER GELÖSCHTEN ZURÜCK
ERSTELLE ODER ERSETZE FUNKTION service_functions.table_delete ( aktuelle_id ganzzahl ) 
GIBT ganzzahl AS $$
DEKLARIERE
  reihen_zahl ganzzahl  ;    
BEGIN
  LÖSCHE AUS store.table WO id = aktuelle_id; 

  HOLEN DIAGNOSTIKEN reihen_zahl = REIHE_ANZAHL;                                                                           

  RÜCKGABE reihen_zahl ;
END
$$ SPRACHE plpgsql SICHERHEIT DEFINIERER;
 
-- DETAILÄNDERUNGEN
-- GIB REIHENNUMMER DER GEÄNDERTEN ZURÜCK
ERSTELLE ODER ERSETZE FUNKTION service_functions.table_update_column 
(
  aktuelle_id ganzzahl 
  ,neue_spalte store.table.column%TYPE
) 
GIBT ganzzahl AS $$
DEKLARIERE
  reihen_zahl ganzzahl  ; 
BEGIN
  UPDATE  store.table
  SET
    spalte = neue_spalte
  WO id = aktuelle_id;

  HOLEN DIAGNOSTIKEN reihen_zahl = REIHE_ANZAHL;                                                                           

  RÜCKGABE reihen_zahl ;
END
$$ SPRACHE plpgsql SICHERHEIT DEFINIERER;

GeschÀftsfunktionen

Entwickelt fĂŒr endgĂŒltige GeschĂ€ftsoperationen, die vom Kunden aufgerufen werden. Gibt immer zurĂŒck — JSON. Zur Abfangung und Protokollierung von AusfĂŒhrungsfehlern wird ein Block verwendet EXCEPTION.

Vorlage — GeschĂ€fts-Funktion

CREATE OR REPLACE FUNCTION business_functions.business_function_template(
-- Eingabeparameter        
 )
RETURNS JSON AS $$
DECLARE
  ------------------------
  -- fĂŒr Ausnahmebehandlung
  error_message text ;
  error_json json ;
  result json ;
  ------------------------ 
BEGIN
-- LOGGING
  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'STARTED',
    json_build_object
    (
	-- Eingabeparameter
    ) 
   );

  PERFORM business_functions.notice('business_function_template');            

  -- BEGINN DES GESCHÄFTSTEILS
  -- ENDE DES GESCHÄFTSTEILS

  -- ERFOLGREICHES RESULTAT
  PERFORM business_functions.notice('result');
  PERFORM business_functions.notice(result);

  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'FINISHED', 
    json_build_object('result', result)
  );

  RETURN result ;
----------------------------------------------------------------------------------------------------------
-- AUSNAHMEN BEHANDLUNG
EXCEPTION                        
  WHEN OTHERS THEN    
    PERFORM loc_audit_functions.make_log
    (
      'business_function_template',
      'STARTED',
      json_build_object
      (
	-- Eingabeparameter	
      ) , TRUE );

     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       'ERROR',
       json_build_object('SQLSTATE', SQLSTATE), TRUE 
     );

     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       'ERROR',
       json_build_object('SQLERRM', SQLERRM), TRUE 
      );

     GET STACKED DIAGNOSTICS error_message = RETURNED_SQLSTATE ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      'ERROR-RETURNED_SQLSTATE', json_build_object('RETURNED_SQLSTATE', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = COLUMN_NAME ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       'ERROR-COLUMN_NAME',
       json_build_object('COLUMN_NAME', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = CONSTRAINT_NAME ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      'ERROR-CONSTRAINT_NAME',
      json_build_object('CONSTRAINT_NAME', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = PG_DATATYPE_NAME ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       'ERROR-PG_DATATYPE_NAME',
       json_build_object('PG_DATATYPE_NAME', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       'ERROR-MESSAGE_TEXT', json_build_object('MESSAGE_TEXT', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = SCHEMA_NAME ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       'ERROR-SCHEMA_NAME', json_build_object('SCHEMA_NAME', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_DETAIL ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      'ERROR-PG_EXCEPTION_DETAIL',
      json_build_object('PG_EXCEPTION_DETAIL', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_HINT ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       'ERROR-PG_EXCEPTION_HINT', json_build_object('PG_EXCEPTION_HINT', error_message), TRUE );

     GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_CONTEXT ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      'ERROR-PG_EXCEPTION_CONTEXT', json_build_object('PG_EXCEPTION_CONTEXT', error_message), TRUE );                                      

    RAISE WARNING 'ALARM: %' , SQLERRM ;

    SELECT json_build_object
    (
      'isError' , TRUE ,
      'errorMsg' , SQLERRM
     ) INTO error_json ;

  RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Zusammenfassung

Um das Gesamtbild zu skizzieren, denke ich, dass das völlig ausreichend ist. Falls jemand an Details oder Ergebnissen interessiert ist, hinterlassen Sie gerne Kommentare, ich fĂŒge das Bild gerne mit weiteren Nuancen hinzu.

P.S.

Protokollierung eines einfachen Fehlers - Typ des Eingabewertes

-[ DATEN 1 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1072
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          | STARTED
jsonb_pretty    | {
                |     "dko": {
                |         "id": 4,
                |         "type": "Type1",
                |         "title": "ERSTELLT VON addKD",
                |         "Weight": 10,
                |         "Tr": "300",
                |         "reduction": 10,
                |         "isTrud": "TRUE",
                |         "description": "Beschreibung",
                |         "lowerTr": "100",
                |         "measurement": "Messung1",
                |         "methodology": "m1",
                |         "passportUrl": "dateien",
                |         "upperTr": "200",
                |         "weightingFactor": 100.123,
                |         "actualTrValue": null,
                |         "upperTrCalcNumber": "120"
                |     },
                |     "CardId": 3
                | }
-[ DATEN 2 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1073
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER
jsonb_pretty    | {
                |     "SQLSTATE": "22P02"
                | }
-[ DATEN 3 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1074
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER
jsonb_pretty    | {
                |     "SQLERRM": "ungĂŒltige Eingabesyntax fĂŒr Typ numerisch: "null""
                | }
-[ DATEN 4 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1075
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-RETURNED_SQLSTATE
jsonb_pretty    | {
                |     "RETURNED_SQLSTATE": "22P02"
                | }
-[ DATEN 5 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1076
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-COLUMN_NAME
jsonb_pretty    | {
                |     "COLUMN_NAME": ""
                | }

-[ DATEN 6 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1077
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-CONSTRAINT_NAME
jsonb_pretty    | {
                |     "CONSTRAINT_NAME": ""
                | }
-[ DATEN 7 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1078
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-PG_DATATYPE_NAME
jsonb_pretty    | {
                |     "PG_DATATYPE_NAME": ""
                | }
-[ DATEN 8 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1079
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-MESSAGE_TEXT
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "ungĂŒltige Eingabesyntax fĂŒr Typ numerisch: "null""
                | }
-[ DATEN 9 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1080
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-SCHEMA_NAME
jsonb_pretty    | {
                |     "SCHEMA_NAME": ""
                | }
-[ DATEN 10 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1081
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-PG_EXCEPTION_DETAIL
jsonb_pretty    | {
                |     "PG_EXCEPTION_DETAIL": ""
                | }
-[ DATEN 11 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1082
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-PG_EXCEPTION_HINT
jsonb_pretty    | {
                |     "PG_EXCEPTION_HINT": ""
                | }
-[ DATEN 12 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1083
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-PG_EXCEPTION_CONTEXT
jsonb_pretty    | {
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEHLER-MESSAGE_TEXT
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "ungĂŒltige Eingabesyntax fĂŒr Typ numerisch: "null""
                | }

Quelle: habr.com

ZuverlĂ€ssiges Webhosting mit DDoS-Schutz, VPS- und VDS-Server kaufen đŸ”„ ZuverlĂ€ssiges Webhosting mit DDoS-Schutz, VPS- und VDS-Server kaufen | ProHoster