Studium dotyczące wdrażania logiki biznesowej na poziomie funkcji przechowywanych PostgreSQL.

Motywacją do napisania tego szkicu była artykuł „W czasie kwarantanny obciążenie wzrosło pięciokrotnie, ale byliśmy gotowi”. Jak Lingualeo przeszedł na PostgreSQL z 23 milionami użytkowników. Zainteresowała mnie również artykuł opublikowany cztery lata temu — Realizacja logiki biznesowej w MySQL.

Zastało mnie to, że ta sama myśl - "zrealizować logikę biznesową w bazie danych".

przyszła do głowy nie tylko mi.

Chciałem także na przyszłość zachować, dla siebie najpierw, interesujące materiały, które pojawiły się w trakcie realizacji. Zwłaszcza biorąc pod uwagę to, że stosunkowo niedawno podjęto strategiczną decyzję o zmianie architektury i przeniesieniu logiki biznesowej na poziom backendu. Tak więc to, co zostało opracowane, wkrótce nikomu nie będzie potrzebne i nikogo nie będzie interesować.

Opisane metody nie są żadnym odkryciem ani wyjątkiem know how, wszystko zgodnie z klasyką i zostało zaimplementowane wielokrotnie (na przykład ja dany sposób zastosowałem 20 lat temu w Oracle). Po prostu postanowiłem wszystko zebrać w jednym miejscu. Nagle komuś się przyda. Jak pokazała praktyka — dość często ta sama idea przychodzi niezależnie różnym ludziom. A dla siebie zostawić na pamiątkę, to również przydatne.

Oczywiście nic w tym świecie nie jest doskonałe, błędy i literówki niestety są możliwe. Krytyka i uwagi są mile widziane i oczekiwane. I jeszcze jeden mały szczegół — konkretne szczegóły realizacji są pominięte. W końcu wszystko jest używane w aktualnie działającym projekcie. Tak więc artykuł traktuje jako szkic oraz opis ogólnej koncepcji, nic więcej. Mam nadzieję, że dla zrozumienia ogólnego obrazu szczegółów jest wystarczająco.

Ogólna idea — „dziel i rządź, ukrywaj i posiadanie”

Idea klasyczna — oddzielna schemat dla tabel, oddzielna schemat dla funkcji przechowywanych.
Klient nie ma dostępu do danych bezpośrednio. Wszystko, co klient może zrobić — to tylko wywołać funkcję przechowywaną i przetworzyć otrzymaną odpowiedź.

Role

CREATE ROLE store;

CREATE ROLE sys_functions;

CREATE ROLE loc_audit_functions;

CREATE ROLE service_functions;

CREATE ROLE business_functions;

Schematy

Schemat przechowywania tabel

Docelowe tabele, które realizują bytowe encje.

CREATE SCHEMA store AUTHORIZATION store;

Schemat funkcji systemowych

Funkcje systemowe, w szczególności do logowania zmian tabel.

CREATE SCHEMA sys_functions AUTHORIZATION sys_functions;

Schemat lokalnego audytu

Funkcje i tabele do realizacji lokalnej audytacji wykonania funkcji składowanych i zmiany tabel docelowych.

CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;

Schemat funkcji serwisowych

Funkcje dla funkcji serwisowych i DML.

CREATE SCHEMA service_functions AUTHORIZATION service_functions;

Schemat funkcji biznesowych

Funkcje dla ostatecznych funkcji biznesowych wywoływanych przez klienta.

CREATE SCHEMA business_functions AUTHORIZATION business_functions;

Prawa dostępu

Rola — DBA ma pełny dostęp do wszystkich schematów (oddzielona od roli właściciela bazy danych).

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;

Rola — USER ma przywilej EXECUTE w schemacie business_functions.

CREATE ROLE user_role;

Przywileje między schematami

GRANT
Ponieważ wszystkie funkcje są tworzone z atrybutem SECURITY DEFINER potrzebna jest instrukcja REVOKE EXECUTE ON ALL FUNCTION… FROM public;

REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA sys_functions FROM public ; 
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA loc_audit_functions FROM public ; 
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA service_functions FROM public ; 
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA business_functions FROM public ; 

GRANT USAGE ON SCHEMA sys_functions TO dba_role ; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sys_functions TO dba_role ;
GRANT USAGE ON SCHEMA loc_audit_functions TO dba_role ; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA loc_audit_functions TO dba_role ;
GRANT USAGE ON SCHEMA service_functions TO dba_role ; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA service_functions TO dba_role ;
GRANT USAGE ON SCHEMA business_functions TO dba_role ; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO user_role ;

GRANT ALL PRIVILEGES ON SCHEMA store TO GROUP business_functions ;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA store TO business_functions ;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA store TO business_functions ;

Tak, schemat bazy danych — gotowy. Można przystąpić do wypełnienia danymi.

Tabele docelowe

Tworzenie tabel jest trywialne. Nie ma żadnych szczególnych cech, poza tym, że zdecydowano się zrezygnować z używania SERIAL i generować sekwencje jawnie. Oczywiście, maksymalne wykorzystanie instrukcji

COMMENT ON ...

Komentarze do wszystkich obiektów, bez wyjątków.

Lokalny audyt

Do prowadzenia dziennika wykonania funkcji składowanych i zmiany tabel docelowych używana jest tabela lokalnego audytu, zawierająca między innymi szczegóły połączenia klienta, etykietę wywoływanego modułu, rzeczywiste wartości parametrów wejściowych i wyjściowych w formacie JSON.

Funkcje systemowe

Służą do logowania zmian w tabelach docelowych. Stanowią funkcje wyzwalające.

Szablon — funkcja systemowa

---------------------------------------------------------
-- WSTAWIENIE
CREATE OR REPLACE FUNCTION sys_functions.table_insert_log ()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM loc_audit_functions.make_log( ' '||'table', 'insert', 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();

---------------------------------------------------------
-- AKTUALIZACJA
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', 'update', 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();

---------------------------------------------------------
-- USUNIĘCIE
CREATE OR REPLACE FUNCTION sys_functions.table_delete_log ()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM loc_audit_functions.make_log( ' '||'table', 'delete', 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();

Funkcje serwisowe

Służą do realizacji operacji serwisowych i DML na docelowych tabelach.

Szablon — funkcja serwisowa

--WSTAWIENIE
--ZWROĆ id NOWEGO WIERSZA
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
  new_id integer;
BEGIN
  -- Generowanie nowego id
  new_id = nextval('store.table.seq');

  -- Wstawienie do tabeli
  INSERT INTO store.table
  ( 
    id,
    column
   )
  VALUES
  (
   new_id,
   new_column
   );

RETURN new_id;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

--USUNIĘCIE
--ZWROĆ NUMERY WIERSZY USUNIĘTYCH
CREATE OR REPLACE FUNCTION service_functions.table_delete ( current_id integer ) 
RETURNS integer AS $$
DECLARE
  rows_count integer;
BEGIN
  DELETE FROM store.table WHERE id = current_id;

  GET DIAGNOSTICS rows_count = ROW_COUNT;

  RETURN rows_count;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- SZCZEGÓŁY AKTUALIZACJI
-- ZWRACA NUMERY ZMIENIONYCH WIERSZY
CREATE OR REPLACE FUNCTION service_functions.table_update_column 
(
  current_id integer
  ,new_column store.table.column%TYPE
) 
RETURNS integer AS $$
DECLARE
  rows_count integer;
BEGIN
  UPDATE store.table
  SET
    column = new_column
  WHERE id = current_id;

  GET DIAGNOSTICS rows_count = ROW_COUNT;

  RETURN rows_count;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Funkcje biznesowe

Służą do końcowych funkcji biznesowych wywoływanych przez klienta. Zawsze zwracają — YANG. Aby przechwycić i zalogować błędy wykonania, używany jest blok WYJĄTEK.

Szablon — funkcja biznesowa

UTWÓRZ LUB ZASTĄP FUNKCJĘ business_functions.business_function_template(
--Parametry wejściowe        
 )
ZWRACA JSON JAKO $$
DECLARE
  ------------------------
  --do obsługi wyjątków
  komunikat_błędu text ;
  json_błędu json ;
  wynik json ;
  ------------------------ 
BEGIN
--LOGOWANIE
  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'ROZPOCZĘTO',
    json_build_object
    (
	--Parametry wewnętrzne
    ) 
   );

  PERFORM business_functions.notice('business_function_template');            

  --ROZPOCZNIJ CZĘŚĆ BIZNESOWĄ
  --ZAKOŃCZ CZĘŚĆ BIZNESOWĄ

  -- POMYŚLNY WYNIK
  PERFORM business_functions.notice('wynik');
  PERFORM business_functions.notice(wynik);

  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'ZAKOŃCZONO', 
    json_build_object( 'wynik',wynik )
  );

  RETURN wynik ;
----------------------------------------------------------------------------------------------------------
-- OBSŁUGA WYJĄTKÓW
EXCEPTION                        
  WHEN OTHERS THEN    
    PERFORM loc_audit_functions.make_log
    (
      'business_function_template',
      'ROZPOCZĘTO',
      json_build_object
      (
	--Parametry wewnętrzne	
      ) , TRUE );

     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' BŁĄD',
       json_build_object('SQLSTATE',SQLSTATE ), TRUE 
     );

     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' BŁĄD',
       json_build_object('SQLERRM',SQLERRM  ), TRUE 
      );

     GET STACKED DIAGNOSTICS komunikat_błędu = RETURNED_SQLSTATE ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' BŁĄD-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = COLUMN_NAME ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' BŁĄD-COLUMN_NAME',
       json_build_object('COLUMN_NAME',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = CONSTRAINT_NAME ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' BŁĄD-CONSTRAINT_NAME',
      json_build_object('CONSTRAINT_NAME',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = PG_DATATYPE_NAME ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' BŁĄD-PG_DATATYPE_NAME',
       json_build_object('PG_DATATYPE_NAME',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = MESSAGE_TEXT ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' BŁĄD-MESSAGE_TEXT',json_build_object('MESSAGE_TEXT',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = SCHEMA_NAME ;
     PERFORM loc_audit_functions.make_log
     (s
       'business_function_template',
       ' BŁĄD-SCHEMA_NAME',json_build_object('SCHEMA_NAME',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = PG_EXCEPTION_DETAIL ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' BŁĄD-PG_EXCEPTION_DETAIL',
      json_build_object('PG_EXCEPTION_DETAIL',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = PG_EXCEPTION_HINT ;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' BŁĄD-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',komunikat_błędu  ), TRUE );

     GET STACKED DIAGNOSTICS komunikat_błędu = PG_EXCEPTION_CONTEXT ;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' BŁĄD-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',komunikat_błędu  ), TRUE );                                      

    RAISE WARNING 'ALARM: %' , SQLERRM ;

    SELECT json_build_object
    (
      'isError' , TRUE ,
      'komunikatBłędu' , SQLERRM
     ) INTO json_błędu ;

  RETURN  json_błędu ;
END
$$ JĘZYK plpgsql DEFINER BEZPIECZEŃSTWA;

Podsumowanie

Aby opisać ogólny obraz, myślę, że to wystarczy. Jeśli kogoś interesują szczegóły i wyniki, piszcie w komentarzach, z przyjemnością uzupełnię obraz dodatkowymi szczegółami.

P.S.

Logowanie prostego błędu — typ parametru wejściowego

-[ REKORD 1 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1072
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          | ROZPOCZĘTY
jsonb_pretty    | {
                |     "dko": {
                |         "id": 4,
                |         "type": "Typ1",
                |         "title": "UTWORZONY PRZEZ addKD",
                |         "Weight": 10,
                |         "Tr": "300",
                |         "reduction": 10,
                |         "isTrud": "PRAWDA",
                |         "description": "opis",
                |         "lowerTr": "100",
                |         "measurement": "mierzenie1",
                |         "methodology": "m1",
                |         "passportUrl": "pliki",
                |         "upperTr": "200",
                |         "weightingFactor": 100.123,
                |         "actualTrValue": null,
                |         "upperTrCalcNumber": "120"
                |     },
                |     "CardId": 3
                | }
-[ REKORD 2 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1073
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD
jsonb_pretty    | {
                |     "SQLSTATE": "22P02"
                | }
-[ REKORD 3 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1074
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD
jsonb_pretty    | {
                |     "SQLERRM": "nieprawidłowa składnia wejściowa dla typu numerycznego: "null""
                | }
-[ REKORD 4 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1075
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-ZWRÓCONY_SQLSTATE
jsonb_pretty    | {
                |     "RETURNED_SQLSTATE": "22P02"
                | }
-[ REKORD 5 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1076
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-NAZWA_KOLUMNY
jsonb_pretty    | {
                |     "COLUMN_NAME": ""
                | }

-[ REKORD 6 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1077
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-NAZWA_KONSTRUKCJI
jsonb_pretty    | {
                |     "CONSTRAINT_NAME": ""
                | }
-[ REKORD 7 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1078
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-NAZWA_TYPU_DANYCH_PG
jsonb_pretty    | {
                |     "PG_DATATYPE_NAME": ""
                | }
-[ REKORD 8 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1079
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-TEXT_WIADOMOŚCI
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "nieprawidłowa składnia wejściowa dla typu numerycznego: "null""
                | }
-[ REKORD 9 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1080
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-NAZWA_SCHEMATU
jsonb_pretty    | {
                |     "SCHEMA_NAME": ""
                | }
-[ REKORD 10 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1081
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-SZCZEGÓŁ_WYJĄTKU_PG
jsonb_pretty    | {
                |     "PG_EXCEPTION_DETAIL": ""
                | }
-[ REKORD 11 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1082
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-WSKAZÓWKA_WYJĄTKU_PG
jsonb_pretty    | {
                |     "PG_EXCEPTION_HINT": ""
                | }
-[ REKORD 12 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1083
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-KONTEKST_WYJĄTKU_PG
jsonb_pretty    | {
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  BŁĄD-TEXT_WIADOMOŚCI
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "nieprawidłowa składnia wejściowa dla typu numerycznego: "null""
                | }

Źródło: habr.com