【DB吐槽大會】第67期 - PG 存儲過程和函數内對自治事務支援不完整

背景

1、産品的問題點

• PG 存儲過程和函數内對自治事務支援不完整

2、問題點背後涉及的技術原理

• PG 的1個函數為1個原子操作, 要麼全部復原, 要麼全部失敗. (注意: exception裡算一個新事務, 觸發exception時, 函數體内的操作全部復原, exception體内的執行如果正常則這個exception體内的變更操作可以送出.)
• 在函數内不能使用commit, rollback, savepoint等事務控制語句.
• 在存儲過程中隻能使用commit, rollback事務控制語句, 不能使用savepoint, rollback to savepoint, release savepoint等語句.

3、這個問題将影響哪些行業以及業務場景

• 使用function, procedure進行複雜業務邏輯處理的場景, 例如分析業務, 報表業務等

4、會導緻什麼問題?

• 無法靈活的處理事務控制

5、業務上應該如何避免這個坑

• 暫時沒有很好的解決方案, 一些類似的替代方案, 都非常難操作

• • 《PostgreSQL 10.0 preview 功能增強 - 匿名、自治事務(Oracle 相容性)(background session)》
  • 《PostgreSQL Oracle 相容性之 - plpgsql 自治事務(autonomous_transaction)更新檔》
  • 《PostgreSQL Oracle 相容性之 - 函數 自治事務 的寫法和實作》
  • 《PostgreSQL Oracle 相容性 自治事務 - partial/sub commit within function》
  • 《Compare dblink module Within One Transaction in PostgreSQL - dblink, 自治事務(Oracle相容性)》

使用exception也很難滿足, 如下, 使用變量、exception 來模拟savepoint和rollback to savepoint的功能, 也很複雜 :

create or replace procedure p (int, int) as $$  
declare  
  v int := $1;  
  retris int := $2;  
  text_var1 text;  
  text_var2 text;  
  text_var3 text;  
  text_var4 text;  
begin  
  if retris >= 3 then   
    raise notice 'retris: %', retris;   
    return;   
  end if;    
    
  if v = 0 then  
    v := 1;  
    insert into a values (1);   
    commit;  
  end if;  
    
  if v = 1 then   
    v := 2;  
    insert into a values (2);  
    commit;  
  end if;   
  
  if v = 2 then   
    v := 3;   
    insert into a values (3);   
    commit;   
  end if;   
  
  if v = 3 then   
    v := 4;   
    insert into a values (4);   
    commit;   
  end if;   
  
exception when others then   
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,  
                          text_var2 = PG_EXCEPTION_DETAIL,  
                          text_var3 = PG_EXCEPTION_HINT,  
                          text_var4 = PG_EXCEPTION_CONTEXT;  
                          raise notice '%,%,%,%', text_var1, text_var2, text_var3, text_var4;  
  commit;  
  call p(v-1, retris+1) ;   
end;   
$$ language plpgsql;   
  
  
  
postgres=# \set VERBOSITY verbose  
postgres=# call p (0,0);  
NOTICE:  00000: cannot commit while a subtransaction is active,,,PL/pgSQL function p(integer,integer) line 18 at COMMIT  
LOCATION:  exec_stmt_raise, pl_exec.c:3906  
NOTICE:  00000: cannot commit while a subtransaction is active,,,PL/pgSQL function p(integer,integer) line 18 at COMMIT  
SQL statement "call p(v-1, retris+1)"  
PL/pgSQL function p(integer,integer) line 45 at CALL  
LOCATION:  exec_stmt_raise, pl_exec.c:3906  
NOTICE:  00000: cannot commit while a subtransaction is active,,,PL/pgSQL function p(integer,integer) line 18 at COMMIT  
SQL statement "call p(v-1, retris+1)"  
PL/pgSQL function p(integer,integer) line 45 at CALL  
SQL statement "call p(v-1, retris+1)"  
PL/pgSQL function p(integer,integer) line 45 at CALL  
LOCATION:  exec_stmt_raise, pl_exec.c:3906  
NOTICE:  00000: retris: 3  
LOCATION:  exec_stmt_raise, pl_exec.c:3906  
CALL  
  
  
  
通過dblink去調用p(int,int), 開啟一個新會話是可以的. 複雜度又增加了.          

6、業務上避免這個坑犧牲了什麼, 會引入什麼新的問題

• 開發門檻非常高.

7、資料庫未來産品疊代如何修複這個坑

• 希望在函數、存儲過程中實作完整的事務控制邏輯. 包括begin;end;savepoint;rollback;commit;release savepoint;rollback to savepoint;等