Hologres:Stored procedure

Limits

• Hologres V3.0 and later support stored procedures that use the PL/pgSQL syntax. For more information about the PL/pgSQL syntax, see SQL Procedural Language.

• Hologres stored procedures allow you to execute multiple data definition language (DDL) statements in a transaction or multiple data manipulation language (DML) statements in a transaction. You cannot execute DDL and DML statements in the same transaction. For more information, see SQL transaction capabilities.

• Stored procedures do not allow you to configure return values. Therefore, stored procedures cannot be used as user-defined functions (UDFs).

Permissions

• If you want to execute the CREATE PROCEDURE statement, you must have the CREATE permission on a database, which is the same as the permission required to execute the CREATE TABLE statement. For more information, see CREATE PROCEDURE.

• If you want to execute the CREATE OR REPLACE PROCEDURE statement, you must have the CREATE permission on a database and the ownership of the stored procedure to be replaced. For more information, see CREATE PROCEDURE.

• If you want to call a stored procedure, you must have the EXECUTE permission on the stored procedure. For more information, see CALL.

Syntax

Hologres stored procedures are compatible with PostgreSQL. This section describes the syntax of Hologres stored procedures.

Create a stored procedure

CREATE [ OR REPLACE ] PROCEDURE
    <procedure_name> ([<argname> <argtype>])
LANGUAGE 'plpgsql'
AS <definition>;

For more information about the parameters, see CREATE PROCEDURE.

Modify a stored procedure

ALTER PROCEDURE <procedure_name> ([<argname> <argtype>])
    OWNER TO <new_owner> | CURRENT_USER | SESSION_USER;

For more information about the parameters, see ALTER PROCEDURE.

Drop a stored procedure

DROP PROCEDURE [ IF EXISTS ] <procedure_name> ([<argname> <argtype>]); 

For more information about the parameters, see DROP PROCEDURE.

Call a stored procedure

CALL <procedure_name> ([<argument>]);

For more information about the parameters, see CALL.

Examples

• Example 1: Create and call a stored procedure that contains multiple DDL statements in a transaction.

  1. Create a stored procedure.

     CREATE OR REPLACE PROCEDURE procedure_1()
     LANGUAGE 'plpgsql'
     AS $$
     BEGIN
         --- TXN1 --- 
         CREATE TABLE a1(key int);
         CREATE TABLE a2(key int);
         COMMIT; 
     
         --- TXN2 ---
         CREATE TABLE a3(key int);
         CREATE TABLE a4(key int);
         ROLLBACK;
     END; 
     $$;

  2. Call the stored procedure. Tables a1 and a2 are created, and tables a3 and a4 are not created.

     CALL procedure_1();

• Example 2: Create and call a stored procedure that contains multiple DML statements in a transaction.

  1. Create stored procedures.

     CREATE OR REPLACE PROCEDURE procedure_2()
     LANGUAGE 'plpgsql'
     AS $$
     BEGIN
         INSERT INTO a1 VALUES(1);
         INSERT INTO a2 VALUES(2);
         ROLLBACK;
     END;
     $$;
     
     CREATE OR REPLACE PROCEDURE procedure_3()
     LANGUAGE 'plpgsql'
     AS $$
     BEGIN
         INSERT INTO a1 VALUES(1);
         INSERT INTO a2 VALUES(2);
     END;
     $$;

  2. Call the stored procedures.

     • Call procedure_2. The rollback operation is supported, and data is not written.

       -- Enable the DML transaction feature.
       SET hg_experimental_enable_transaction = ON;
       
       -- Call the stored procedure.
       CALL procedure_2();

     • Call procedure_3. Data is written.

       -- Enable the DML transaction feature.
       SET hg_experimental_enable_transaction = ON;
       
       -- Call the stored procedure.
       CALL procedure_3();

• Example 3: Create and call a stored procedure that contains DDL and DML statements.

  1. Create a stored procedure in which DDL and DML statements are separately committed. Hologres does not allow you to execute DDL and DML statements in the same transaction.

     CREATE OR REPLACE PROCEDURE procedure_4()
     LANGUAGE 'plpgsql'
     AS $$
     BEGIN
         INSERT INTO a1 VALUES(1);
         COMMIT;	
         CREATE TABLE bb(key int);
         COMMIT;	
         INSERT INTO a1 VALUES(2);
         INSERT INTO bb VALUES(1);    
         COMMIT;	
     END;
     $$;

  2. Call the stored procedure. Table creation and data writes are successful.

     -- Enable the DML transaction feature.
     SET hg_experimental_enable_transaction = ON;
     
     -- Call the stored procedure.
     CALL procedure_4();

• Example 4: Create and call a stored procedure that contains common clauses, such as clauses for defining input parameters, intermediate variables, loops, IF conditions, and exceptions.

  1. Create a stored procedure.

     CREATE OR REPLACE PROCEDURE procedure_5(input text)
     LANGUAGE 'plpgsql'
     AS $$
     -- Define an intermediate variable.
     DECLARE
     sql1 text;
     BEGIN
         -- Insert a row of data into the table of input parameters.
         EXECUTE 'insert into ' || input || ' values(1);';
         COMMIT;
     
         -- Create a table named a3.
         CREATE TABLE a3(key int);
         COMMIT;
     
         -- Use the intermediate variable to write a data record to the a3 table.
         sql1 = 'insert into a3 values(1);';
         EXECUTE sql1;
     
         -- Define a FOR loop.
         FOR i IN 1..10 LOOP
             BEGIN
                 -- i=1 already exists in the table, and one record is logged.
                 IF i IN (SELECT KEY FROM a3) THEN
                     RAISE NOTICE 'Data already exists.';
                 -- Other values do not exist in the table. The session tries to write the values to the table and uses RAISE EXCEPTION to throw an exception. Then, the commit operation is performed.
                 ELSE
                     INSERT INTO a3 VALUES(i);
                     RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                     COMMIT; 
                 END IF;
             -- The reported exception is logged.
             EXCEPTION 
                 WHEN OTHERS THEN
                     RAISE NOTICE 'Catch error.';
             END;
         END LOOP;
     
     END;
     $$;

  2. Call the stored procedure. Only value 1 is written to the a3 table, and related logs are recorded.

     -- Enable the DML transaction feature.
     SET hg_experimental_enable_transaction = ON;
     
     -- Call the stored procedure.
     CALL procedure_5('a1');

Manage stored procedures

• Query created stored procedures.

  SELECT
      p.proname AS procedure_name,
      pg_get_function_identity_arguments(p.oid) AS argument_types,
      REPLACE(pg_get_functiondef(p.oid),'$procedure$','$$') AS procedure_detail,
      n.nspname AS schema_name,
      r.rolname AS owner_name,
      d.description AS description
  FROM
      pg_proc p
      INNER JOIN pg_namespace n ON p.pronamespace = n.oid
      INNER JOIN pg_roles r ON p.proowner = r.oid
      LEFT JOIN pg_description d ON p.oid = d.objoid
  WHERE
      r.rolname != 'holo_admin'
      AND p.prokind = 'p'
  ORDER BY
      n.nspname,
      p.proname;

• Query the definition of a stored procedure.

  SELECT pg_get_functiondef('<procedure_name>'::regproc);

Troubleshooting

Hologres is a distributed system. Therefore, the frontend (FE) nodes of a Hologres instance are deployed in a distributed manner. The metadata of FE nodes must be synchronized in real time. If a DDL operation is performed on a table but metadata is not synchronized, the DDL operation may fail. In this case, Hologres automatically retries the DDL operation when the automatic retry feature is supported. No manual operation is required. However, in stored procedures, the error message "HG_PLPGSQL_NEED_RETRY" is returned because the automatic retry feature is not supported.

We recommend that you manually define the retry logic for tables on which DDL operations are frequently performed in stored procedures. This helps prevent error messages from being frequently reported. Sample code:

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- The session attempts to execute the DDL statement and exits the loop when the execution is successful.
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- If the error message "HG_PLPGSQL_NEED_RETRY" is reported, the session logs the error and automatically retries the operation.
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;