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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot