Studie om genomförande av affärslogik på nivån av lagrade funktioner i PostgreSQL

Inspiration for writing the study came from the article ‘During the quarantine, the load increased fivefold, but we were prepared.’ How Lingualeo migrated to PostgreSQL with 23 million users. An interesting article published four years ago also caught my attention — Implementing business logic in MySQL.

It was interesting that the same thoughtto implement business logic in the database".

occurred to me as well.

In addition, I wanted to keep some interesting developments that arose during the implementation for myself, especially considering that a strategic decision was recently made to change the architecture and move the business logic to the backend level. Thus, everything that was developed will soon be of no use and of no interest to anyone.

The methods described are not some kind of discovery or exceptional know how, everything is classic and has been implemented many times (for example, I applied a similar approach 20 years ago on Oracle). I just decided to compile everything in one place. Maybe it will be useful to someone. As practice has shown — quite often the same idea comes independently to different people. And it’s useful to keep it as a memory for myself.

Of course, nothing in this world is perfect; errors and typos are unfortunately possible. Criticism and comments are always welcomed and expected. And one more small detail — specific implementation details are omitted. After all, everything is still being used in a working project. So, the article serves as a study and description of the general concept, nothing more. I hope the details are sufficient for understanding the overall picture.

The general idea — ‘divide and conquer, hide and possess’

The idea is classic — a separate schema for tables, a separate schema for stored functions.
The client does not have direct access to the data. All the client can do is call a stored function and process the received response.

Roller

CREATE ROLE store;

CREATE ROLE sys_functions;

CREATE ROLE loc_audit_functions;

CREATE ROLE service_functions;

CREATE ROLE business_functions;

Schemas

Table storage schema

Target tables implementing subject entities.

CREATE SCHEMA store AUTHORIZATION store ;

System functions schema

System functions, in particular for logging table changes.

CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;

Local audit schema

Funktioner och tabeller för att implementera lokal granskning av utförandet av sparade funktioner och ändringar av mål-tabeller.

SKAPA SCHEMA loc_audit_functions AUTORISATION loc_audit_functions;

Schema för servicetjänster

Funktioner för service- och DML-funktioner.

SKAPA SCHEMA service_functions AUTORISATION service_functions;

Schema för affärsfunktioner

Funktioner för slutliga affärsfunktioner som anropas av kunden.

SKAPA SCHEMA business_functions AUTORISATION business_functions;

Åtkomstbehörigheter

Roll — DBA har full tillgång till alla scheman (åtskilt från rollen DB Owner).

SKAPA ROLL dba_role;
BEVILJA store TILL dba_role;
BEVILJA sys_functions TILL dba_role;
BEVILJA loc_audit_functions TILL dba_role;
BEVILJA service_functions TILL dba_role;
BEVILJA business_functions TILL dba_role;

Roll — USER har privilegiet KÖR i schemat business_functions.

SKAPA ROLL user_role;

Privilegier mellan scheman

GRANT
Eftersom alla funktioner skapas med attributet SECURITY DEFINER krävs instruktionen ÅTERKALLA KÖR PÅ ALLA FUNKTIONER… FRÅN public;

ÅTERKALLA KÖR PÅ ALLA FUNKTIONER I SCHEMA sys_functions FRÅN public ; 
ÅTERKALLA KÖR PÅ ALLA FUNKTIONER I SCHEMA loc_audit_functions FRÅN public ; 
ÅTERKALLA KÖR PÅ ALLA FUNKTIONER I SCHEMA service_functions FRÅN public ; 
ÅTERKALLA KÖR PÅ ALLA FUNKTIONER I SCHEMA business_functions FRÅN public ; 

BEVILJA ANVÄNDNING PÅ SCHEMA sys_functions TILL dba_role ; 
BEVILJA KÖR PÅ ALLA FUNKTIONER I SCHEMA sys_functions TILL dba_role ;
BEVILJA ANVÄNDNING PÅ SCHEMA loc_audit_functions TILL dba_role ; 
BEVILJA KÖR PÅ ALLA FUNKTIONER I SCHEMA loc_audit_functions TILL dba_role ;
BEVILJA ANVÄNDNING PÅ SCHEMA service_functions TILL dba_role ; 
BEVILJA KÖR PÅ ALLA FUNKTIONER I SCHEMA service_functions TILL dba_role ;
BEVILJA ANVÄNDNING PÅ SCHEMA business_functions TILL dba_role ; 
BEVILJA KÖR PÅ ALLA FUNKTIONER I SCHEMA business_functions TILL dba_role ;
BEVILJA KÖR PÅ ALLA FUNKTIONER I SCHEMA business_functions TILL user_role ;

BEVILJA ALLA PRIVILEGIER PÅ SCHEMA store TILL GRUPP business_functions ;
BEVILJA ALLA PRIVILEGIER PÅ ALLA TABELLER I SCHEMA store TILL business_functions ;
BEVILJA ANVÄNDNING PÅ ALLA SEKVENSER I SCHEMA store TILL business_functions ;

Så, databasschemat är klart. Du kan börja fylla det med data.

Mål-tabeller

Skapandet av tabeller är trivialt. Inga särdrag, förutom att det beslutades att avstå från att använda SERIAL och generera sekvenser explicit. Dessutom, självklart, maximalt utnyttjande av instruktionen

COMMENT ON ...

Kommentarer för alla objekt, utan undantag.

Lokal granskning

För att logga körningen av sparade funktioner och ändringar av mål-tabeller används en lokal gransknings-tabell som inkluderar detaljer om kundanslutningen, märke för den anropade modulen, faktiska värden för in- och utgångsparametrar i form av JSON.

Systemfunktioner

Avsedda för loggning av ändringar i mål-tabeller. Utgör triggerfunktioner.

Mall — systemfunktion

---------------------------------------------------------
-- INFÖR
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();

---------------------------------------------------------
-- UPPDATERA
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();

---------------------------------------------------------
-- TA BORT
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 ();

Tjänstefunktioner

Är avsedda för att genomföra tjänstrelaterade och DML-operationer på måltabellerna.

Mall — tjänstefunktion

--INFÖR
--RETURERA id FÖR NY RAD
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
  new_id integer ;
BEGIN
  -- Generera nytt id
  new_id = nextval('store.table.seq');

  -- Införa i tabellen
  INSERT INTO store.table
  ( 
    id ,
    column
   )
  VALUES
  (
   new_id ,
   new_column
   );

RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

--TA BORT
--RETURERA RADNUMMER SOM HAR TAGITS BORT
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;
 
-- UPPDATERA DETALJER
-- RETURERA RADNUMMER SOM HAR UPPDATERATS
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;

Affärsfunktioner

Är avsedda för slutliga affärsfunktioner som anropas av klienten. Återvänder alltid — JSON. För att fånga och logga körningsfel används ett block EXCEPTION.

Mall — affärsfunktion

SKAPA ELLER ERSÄTT FUNKTION business_functions.business_function_template(
--Ingångsparameter        
 )
RETURNERAR JSON AS $$
DEKLARERA
  ------------------------
  --för undantagshantering
  error_message text ;
  error_json json ;
  result json ;
  ------------------------ 
BÖRJA
--LOGGNING
  UTFÖR loc_audit_functions.make_log
  (
    'business_function_template',
    'STARTAD',
    json_build_object
    (
	--IN Parametrar
    ) 
   );

  UTFÖR business_functions.notice('business_function_template');            

  --STARTA AFFÄRSDELEN
  --AVSLUTA AFFÄRSDELEN

  -- FRAMGÅNGSRESULTAT
  UTFÖR business_functions.notice('result');
  UTFÖR business_functions.notice(result);

  UTFÖR loc_audit_functions.make_log
  (
    'business_function_template',
    'AVSLUTAD', 
    json_build_object( 'result',result )
  );

  RETURNERA result ;
----------------------------------------------------------------------------------------------------------
-- UNDANTAGSHANTERING
UNDANTAG                        
  NÄR ANDRA DÅ    
    UTFÖR loc_audit_functions.make_log
    (
      'business_function_template',
      'STARTAD',
      json_build_object
      (
	--IN Parametrar	
      ) , TRUE );

     UTFÖR loc_audit_functions.make_log
     (
       'business_function_template',
       ' FEL',
       json_build_object('SQLSTATE',SQLSTATE ), TRUE 
     );

     UTFÖR loc_audit_functions.make_log
     (
       'business_function_template',
       ' FEL',
       json_build_object('SQLERRM',SQLERRM  ), TRUE 
      );

     HÄMTA STACKAD DIAGNOSTIK error_message = RETURNED_SQLSTATE ;
     UTFÖR loc_audit_functions.make_log
     (
      'business_function_template',
      ' FEL-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = COLUMN_NAME ;
     UTFÖR loc_audit_functions.make_log
     (
       'business_function_template',
       ' FEL-KOLUMN_NAMN',
       json_build_object('COLUMN_NAME',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = CONSTRAINT_NAME ;
     UTFÖR loc_audit_functions.make_log
     (
      'business_function_template',
      ' FEL-KONSTRIKT_NAMN',
      json_build_object('CONSTRAINT_NAME',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = PG_DATATYPE_NAME ;
     UTFÖR loc_audit_functions.make_log
     (
       'business_function_template',
       ' FEL-PG_DATATYPE_NAME',
       json_build_object('PG_DATATYPE_NAME',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = MESSAGE_TEXT ;
     UTFÖR loc_audit_functions.make_log
     (
       'business_function_template',
       ' FEL-MEDDELANDE_TEXT',json_build_object('MESSAGE_TEXT',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = SCHEMA_NAME ;
     UTFÖR loc_audit_functions.make_log
     (
       'business_function_template',
       ' FEL-SCHEMA_NAMN',json_build_object('SCHEMA_NAME',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = PG_EXCEPTION_DETAIL ;
     UTFÖR loc_audit_functions.make_log
     (
      'business_function_template',
      ' FEL-PG_EXCEPTION_DETAIL',
      json_build_object('PG_EXCEPTION_DETAIL',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = PG_EXCEPTION_HINT ;
     UTFÖR loc_audit_functions.make_log
     (
       'business_function_template',
       ' FEL-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',error_message  ), TRUE );

     HÄMTA STACKAD DIAGNOSTIK error_message = PG_EXCEPTION_CONTEXT ;
     UTFÖR loc_audit_functions.make_log
     (
      'business_function_template',
      ' FEL-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',error_message  ), TRUE );                                      

    HÖJ VARNING 'ALARM: %' , SQLERRM ;

    VÄLJ json_build_object
    (
      'isError' , TRUE ,
      'errorMsg' , SQLERRM
     ) INTO error_json ;

  RETURNERA  error_json ;
SLUT
$$ SPRÅK plpgsql SÄKERHET DEFINERARE;

Resultat

För att beskriva den övergripande bilden, tycker jag att det är tillräckligt. Om någon är intresserad av detaljer och resultat, skriv gärna i kommentarerna, så kompletterar jag gärna bilden med ytterligare nyanser.

P.S.

Loggning av en enkel felaktighet - typ av ingående parameter

-[ POST 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": "SKAPAD AV addKD",
                |         "Weight": 10,
                |         "Tr": "300",
                |         "reduction": 10,
                |         "isTrud": "TRUE",
                |         "description": "beskrivning",
                |         "lowerTr": "100",
                |         "measurement": "mätning1",
                |         "methodology": "m1",
                |         "passportUrl": "filer",
                |         "upperTr": "200",
                |         "weightingFactor": 100.123,
                |         "actualTrValue": null,
                |         "upperTrCalcNumber": "120"
                |     },
                |     "CardId": 3
                | }
-[ POST 2 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1073
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL
jsonb_pretty    | {
                |     "SQLSTATE": "22P02"
                | }
-[ POST 3 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1074
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL
jsonb_pretty    | {
                |     "SQLERRM": "ogiltig inmatningssyntax för typ numerisk: "null""
                | }
-[ POST 4 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1075
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-RETURNED_SQLSTATE
jsonb_pretty    | {
                |     "RETURNED_SQLSTATE": "22P02"
                | }
-[ POST 5 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1076
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-KOLUMN_NAMN
jsonb_pretty    | {
                |     "COLUMN_NAME": ""
                | }
-[ POST 6 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1077
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-BEGRÄNSNING_NAMN
jsonb_pretty    | {
                |     "CONSTRAINT_NAME": ""
                | }
-[ POST 7 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1078
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-PG_DATATYPE_NAMN
jsonb_pretty    | {
                |     "PG_DATATYPE_NAME": ""
                | }
-[ POST 8 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1079
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-MEDDDELANDE_TEXT
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "ogiltig inmatningssyntax för typ numerisk: "null""
                | }
-[ POST 9 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1080
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-SCHEMA_NAMN
jsonb_pretty    | {
                |     "SCHEMA_NAME": ""
                | }
-[ POST 10 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1081
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-PG_UNDANTAG_DETALJ
jsonb_pretty    | {
                |     "PG_EXCEPTION_DETAIL": ""
                | }
-[ POST 11 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1082
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-PG_UNDANTAG_HINT
jsonb_pretty    | {
                |     "PG_EXCEPTION_HINT": ""
                | }
-[ POST 12 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1083
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-PG_UNDANTAG_KONTEXT
jsonb_pretty    | {
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  FEL-MEDDDELANDE_TEXT
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "ogiltig inmatningssyntax för typ numerisk: "null""
                | }

Källa: habr.com