IMMUTABLE和SHIPPABLE

IMMUTABLE是存储过程的一个属性，用于声明该存储过程的结果仅依赖于输入参数，而不依赖于数据库的当前状态。在某些场景下，使用 IMMUTABLE 属性的存储过程可能会被优化为仅执行一次，如果使用不当，可能会造成不符合预期的结果。

SHIPPABLE是存储过程的另一个属性，用于表示该存储过程是否可以下推到 DN（数据节点）执行。如果下推的存储过程访问表或数据库状态，可能会导致数据不一致。

建议在使用 IMMUTABLE 和 SHIPPABLE 属性的存储过程和函数时，避免访问表或数据库信息，以确保其行为符合预期并维护数据一致性。属性详情请参见《开发指南》中“SQL参考 > SQL语法 > C > CREATE FUNCTION”章节。

gaussdb=# create schema best_practices_for_procedure;
CREATE SCHEMA
gaussdb=# create table best_practices_for_procedure.tb1(a int, b int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# create or replace procedure best_practices_for_procedure.proc1(a int, b int) immutable as
begin
insert into best_practices_for_procedure.tb1 values(a, b); --仅做示例，不推荐使用。
end;
/
CREATE PROCEDURE
gaussdb=# call best_practices_for_procedure.proc1(2, 5);
ERROR:  INSERT is not allowed in a non-volatile function
CONTEXT:  SQL statement "insert into best_practices_for_procedure.tb1 values(a, b)"
PL/pgSQL function best_practices_for_procedure.proc1(integer,integer) line 3 at SQL statement
gaussdb=# create or replace function best_practices_for_procedure.func1(a int, b int) return int immutable as
begin
return a * b;
end;
/
CREATE PROCEDURE
gaussdb=# call best_practices_for_procedure.func1(2, 5);
 func1
-------
    10
(1 row)

gaussdb=# create or replace procedure best_practices_for_procedure.proc2(a int, b int) shippable as
begin
insert into best_practices_for_procedure.tb1 values(a, b); --仅做示例，不推荐使用。
end;
/
CREATE PROCEDURE
gaussdb=# call best_practices_for_procedure.proc2(2, 5);
 proc2
-------

(1 row)

gaussdb=# create or replace function best_practices_for_procedure.func2(a int, b int) return int shippable as
begin
return a * b;
end;
/
CREATE PROCEDURE
gaussdb=# call best_practices_for_procedure.func2(2, 5);
 func2
-------
    10
(1 row)

gaussdb=# drop schema best_practices_for_procedure cascade;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table best_practices_for_procedure.tb1
drop cascades to function best_practices_for_procedure.proc1(integer,integer)
drop cascades to function best_practices_for_procedure.func1(integer,integer)
drop cascades to function best_practices_for_procedure.proc2(integer,integer)
drop cascades to function best_practices_for_procedure.func2(integer,integer)
DROP SCHEMA