【DB吐槽大会】第67期 - PG 存储过程和函数内对自治事务支持不完整

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), 开启一个新会话是可以的. 复杂度又增加了.