PolarDB:Stored procedures

How it works

Stored procedures are persisted in the Meta Center. When called, a procedure is loaded to a compute node as needed. SQL statements inside the procedure are forwarded to the SQL engine for execution, while control flow and other procedural logic run in the procedural language (PL) engine.

Before execution begins, the procedure is registered with the runtime procedure manager, which enforces memory limits on the procedure.

Prerequisites

Before you begin, ensure that you have:

• PolarDB-X V5.4.15 or later

Limitations

Exception handler example:

CREATE PROCEDURE pro_test()
  BEGIN
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT data, id FROM t1 ORDER BY id;
  DECLARE cur2 CURSOR FOR SELECT id FROM t2 ORDER BY id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN LEAVE read_loop; END;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF b < c THEN
      INSERT INTO t3 VALUES (b, a);
    ELSE
      INSERT INTO t3 VALUES (c, a);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
  END;

Syntax

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

CALL procedure_name(param1, param2, ...);

DROP PROCEDURE [IF EXISTS] procedure_name;

Manage stored procedures

SELECT * FROM information_schema.Routines WHERE ROUTINE_TYPE = 'PROCEDURE';

-- Option 1: show status
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];

-- Option 2: show definition
SHOW CREATE PROCEDURE procedure_name;

-- Option 3: query information_schema
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'procedure_name';

SHOW PROCESSLIST WHERE info LIKE 'call%';

+------+------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+
| Id   | User | Host            | db   | Command | Time | State | Info                                         | TraceId          |
+------+------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+
|  710 | test | 127.0.0.1:53951 | test | Query   |   79 |       | CALL test.test_procedure: executing pl logic | 14c93b7c7bf00000 |
+------+------+-----------------+------+---------+------+-------+----------------------------------------------+------------------+

KILL {QUERY | CONNECTION} connection_id;

Manage the procedure cache

All metadata of a stored procedure (whether the procedure exists) is stored in the cache. The specific content of the stored procedure is only loaded to the cache when needed.

The SIZE column in cache output is in bytes.

View the cache:

SHOW PROCEDURE CACHE;

Sample output:

+--------------------+-------------------------+-----------+------+
| ID                 | SCHEMA                  | PROCEDURE | SIZE |
+--------------------+-------------------------+-----------+------+
| 10.215.21.156:9090 | test                    | bug6129   | 46   |
| 10.215.21.156:9090 | drds_polarx1_qatest_app | pro_test  | 110  |
+--------------------+-------------------------+-----------+------+

Set the cache size limit:

RESIZE PROCEDURE CACHE num;

When the total size of cached procedures exceeds num bytes, PolarDB-X releases memory from procedures so that the overall size does not exceed the limit. For example:

RESIZE PROCEDURE CACHE 150;
-- Query OK, 0 rows affected (0.10 sec)

SHOW PROCEDURE CACHE;
+--------------------+-------------------------+-----------+------+
| ID                 | SCHEMA                  | PROCEDURE | SIZE |
+--------------------+-------------------------+-----------+------+
| 10.215.21.156:9090 | test                    | bug6129   | 46   |
| 10.215.21.156:9090 | drds_polarx1_qatest_app | pro_test  | 0    |
+--------------------+-------------------------+-----------+------+
-- pro_test memory released so total stays within 150 bytes

Clear the cache:

CLEAR PROCEDURE CACHE;

This releases all the memory consumed by the stored procedures.

CLEAR PROCEDURE CACHE;
-- Query OK, 0 rows affected (0.08 sec)

SHOW PROCEDURE CACHE;
+--------------------+-------------------------+-----------+------+
| ID                 | SCHEMA                  | PROCEDURE | SIZE |
+--------------------+-------------------------+-----------+------+
| 30.225.XX.XX:9090  | test                    | bug6129   | 0    |
| 30.225.XX.XX:9090  | drds_polarx1_qatest_app | pro_test  | 0    |
+--------------------+-------------------------+-----------+------+

Reload procedures:

RELOAD PROCEDURES;

Manage memory usage

During execution, most memory is consumed by cached cursors. PolarDB-X enforces two limits: