IMMUTABLE and SHIPPABLE

IMMUTABLE is an attribute used to declare that the result of a stored procedure is determined solely by input parameters and remains independent of the database status. In certain scenarios, stored procedures with the IMMUTABLE attribute may be optimized to execute only once, and improper use may lead to unexpected results.

Another attribute for stored procedures is SHIPPABLE, which specifies whether the stored procedures can be pushed down to DNs for execution. If the pushed-down stored procedure accesses the table or database status, data inconsistency may occur.

When using stored procedures and functions with the IMMUTABLE and SHIPPABLE attributes, you are advised to avoid accessing information in tables or databases to ensure that the behavior meets expectations and maintain data consistency. For details about attributes, see "SQL Reference > SQL Syntax > C > CREATE FUNCTION" in Developer Guide.

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); -- This is only an example and is not recommended.
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); -- This is only an example and is not recommended.
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