【速记】Postgresql游标短暂的一生

mingjie

发布于 2023-04-18 10:45:12

3470

发布于 2023-04-18 10:45:12

例如Postgresql中有下面一个游标curs4：

drop table if exists t_plpgsql_transaction_20230406_01;
drop table if exists t_plpgsql_transaction_20230406_02;
create table t_plpgsql_transaction_20230406_01(a int);
create table t_plpgsql_transaction_20230406_02(a float);

CREATE or replace PROCEDURE p_inner_20230406()
LANGUAGE plpgsql
AS $$
DECLARE
    carry float;
    cnt int[];
    curs4 refcursor;
    res t_plpgsql_transaction_20230406_01%ROWTYPE;
BEGIN
    open curs4 FOR SELECT * FROM t_plpgsql_transaction_20230406_01;
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (16);
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (17);
    
    fetch curs4 into res; RAISE NOTICE 'curs4 : %', res;
    carry := 1 / 0;
    
EXCEPTION WHEN others THEN 
    commit;
    raise notice 'exception';
END;
$$;

call p_inner_20230406();

创建

curs4的在exec_stmt_open中被创建出来，创建时使用CreatePortal返回一个游标Portal：

注意创建时有一层子事务，exception-transaction，事务堆栈两层。所以游标Portal归属于第二层exception-transaction上。 Portal的resowner也挂在exception-transaction下面。

创建完resowner的样子：

     TopTransactionResourceOwner
     / 
 SubTransaction    ->    Portal（函数执行portal）
    /
  Portal（游标portal）

释放

1/0发生异常后，会自动回滚第二层exception-transaction，游标跟随exception-transaction释放：

注意Portal的resowner只是指向resowner树的某一个位置，释放时resowner不随potal释放，而是随事务的resowner树释放。
释放顺序：先在AbortSubTransaction把portal->resowner置为空；然后CleanupSubTransaction中完成resowner的释放，注意释放只会释放SubTransaction的resowner，游标的res是跟随释放的。

     TopTransactionResourceOwner
     / 
 SubTransaction（释放）    ->    Portal（函数执行portal）
    /
  Portal（游标portal）（释放）

释放完

     TopTransactionResourceOwner
           / 
         Portal（函数执行portal）

内层函数声明的游标无法给外层函数使用

drop table if exists t_plpgsql_transaction_20230406_01;
drop table if exists t_plpgsql_transaction_20230406_02;
create table t_plpgsql_transaction_20230406_01(a int);
create table t_plpgsql_transaction_20230406_02(a float);

CREATE or replace PROCEDURE p_inner_20230406()
LANGUAGE plpgsql
AS $$
DECLARE
    carry float;
    cnt int[];
    curs4 refcursor;
    res t_plpgsql_transaction_20230406_01%ROWTYPE;
BEGIN
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (16);
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (17);
    open curs4 FOR SELECT * FROM t_plpgsql_transaction_20230406_01;
    -- carry := 1 / 0;  
EXCEPTION WHEN others THEN 
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (16);
    open curs4 FOR SELECT * FROM t_plpgsql_transaction_20230406_01;
    fetch curs4 into res; RAISE NOTICE 'curs4 : %', res;
    raise notice 'exception exception';
END;
$$;

CREATE or replace PROCEDURE p_outter_20230406()
LANGUAGE plpgsql
AS $$
DECLARE
    carry float;
    cnt int[];
    curs4 refcursor;
    res t_plpgsql_transaction_20230406_01%ROWTYPE;
BEGIN
    call p_inner_20230406();
    fetch curs4 into res; RAISE NOTICE 'curs4 : %', res;
END;
$$;



call p_outter_20230406();

结果

postgres=# call p_outter_20230406();
ERROR:  cursor variable "curs4" is null
CONTEXT:  PL/pgSQL function p_outter_20230406() line 9 at FETCH

内层函数exception声明的游标无法给外层函数使用

drop table if exists t_plpgsql_transaction_20230406_01;
drop table if exists t_plpgsql_transaction_20230406_02;
create table t_plpgsql_transaction_20230406_01(a int);
create table t_plpgsql_transaction_20230406_02(a float);

CREATE or replace PROCEDURE p_inner_20230406()
LANGUAGE plpgsql
AS $$
DECLARE
    carry float;
    cnt int[];
    curs4 refcursor;
    res t_plpgsql_transaction_20230406_01%ROWTYPE;
BEGIN
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (16);
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (17);
    carry := 1 / 0;  
EXCEPTION WHEN others THEN 
    INSERT INTO t_plpgsql_transaction_20230406_01 (a) VALUES (16);
    open curs4 FOR SELECT * FROM t_plpgsql_transaction_20230406_01;
    fetch curs4 into res; RAISE NOTICE 'curs4 : %', res;
    raise notice 'exception exception';
END;
$$;

CREATE or replace PROCEDURE p_outter_20230406()
LANGUAGE plpgsql
AS $$
DECLARE
    carry float;
    cnt int[];
    curs4 refcursor;
    res t_plpgsql_transaction_20230406_01%ROWTYPE;
BEGIN
    call p_inner_20230406();
    fetch curs4 into res; RAISE NOTICE 'curs4 : %', res;
END;
$$;



call p_outter_20230406();

结果

postgres=# call p_outter_20230406();
NOTICE:  curs4 : (16)
NOTICE:  exception exception
ERROR:  cursor variable "curs4" is null
CONTEXT:  PL/pgSQL function p_outter_20230406() line 9 at FETCH

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-04-12，如有侵权请联系 cloudcommunity@tencent.com 删除

postgresql