Skip to content

Latest commit

 

History

History
102 lines (80 loc) · 3.49 KB

20220702_02.md

File metadata and controls

102 lines (80 loc) · 3.49 KB

PostgreSQL(11.x及以后)中Procedure和Function使用上的一些差别

作者

iihero

日期

2022-07-02

标签

PostgreSQL, Store Procedure, Function

背景

说到Procedure和Function， 是由一个小故事引起的。在PostgreSQL9.x的时候，基本上两者在用法上也没什么大的差别，反正是不能在代码块里头commit transaction。但是在使用PostgreSQL 12的时候，开发人员误以为两者一样可以直接在里边commit，还特意为此写了function，结果发现调用以后的结果不是预期结果。

实际上，PostgreSQL在11.x开始，在Procedure里头就可以调用commit了，这是与function最显著的一个区别。

CREATE OR REPLACE PROCEDURE sapcf_clean_expired_ml_identity(IN expired timestamp, IN batch_count integer) AS $$
DECLARE delete_count BIGINT := 1;
DECLARE total_count BIGINT := 0;
BEGIN
    LOOP
        DELETE FROM ml_identity WHERE ctid IN (SELECT ctid FROM ml_identity b WHERE b.LAST_CHECK_IN < expired AND (b.ID_TYPE = 'RQ' OR b.ID_TYPE = 'CN') LIMIT batch_count);
        GET DIAGNOSTICS delete_count = ROW_COUNT;
        RAISE NOTICE 'Deleted % rows at %', delete_count, now();
        COMMIT;
        total_count = total_count + delete_count;
        exit when delete_count<=0;
    END LOOP;
    RAISE NOTICE 'Totally deleted % rows in the end on: %', total_count, now();
END;
$$ LANGUAGE plpgsql;

-- CALL example
-- call sapcf_clean_expired_ml_identity('2021-12-01 00:00:00',  2000)

2022.2.16 9:30执行：
Deleted 2000 rows at 2022-02-16 09:31:33.860666+08
......
Totally deleted 22793793 rows in the end on: 2022-02-16 09:37:23.308965+08
totally: 5:50seconds

上边的示例，如果我们使用的是funtion, 那么中间的COMMIT地部分就会出问题了。

简单示例

create table t123 (id int primary key, col2 varchar(36));
insert into t123 select generate_series(1,10000), md5(random()::text);
select * from t123 limit 5;

针对这个简单的表，顺序随机插入10000条记录。

然后我们按照常规思路，写一个"Function"，示意插入一条记录：

create or replace function createRow(in v_id integer, in v_col2 varchar(32)) returns int as $$
declare affected_count int;
begin 
	insert into t123 values(v_id, v_col2);
	GET DIAGNOSTICS affected_count = ROW_COUNT;
	RAISE NOTICE 'affected % rows at %', affected_count, now();
    COMMIT;
    return v_id;
    exception when others then 
		raise notice '% %', SQLERRM, SQLSTATE;
		return -1;
end;
$$ language plpgsql;

请注意上边有一条COMMIT。我们看看运行结果：
select createRow(1, 'wang') 结果是

createrow
-1

再看看message提示：
duplicate key value violates unique constraint "t123_pkey" 23505 看起来也对。因为在COMMIT之前就已经出错了。
我们再提交一条正常的插入操作试试，

select createRow(99999, 'wang');

发现结果仍然是：

createrow
-1

message提示居然是：
invalid transaction termination 2D000 上边如果把COMMIT;一行去掉，就能正常插入了。但这很可能不是我们想要的处理事务的预期。

碰到这种情况，最好都写成Procedure的形式，事务由代码块自己控制。

Flag Counter