Global PL/SQL Cache Functions

invalidate_plsql_object(), invalidate_plsql_object(schema, objname, objtype);

Description: Invalidates objects in the global PL/SQL cache. This function is available only when enable_global_plsqlcache is set to on. The user who calls this function must have the SYSADMIN permission.

Parameters: This function is an overloaded function. If there is no input parameter, all global cache objects in all databases are invalidated.

If the parameters schema, objname, and objtype are specified, the specified global cache object in the current database becomes invalid. schema indicates the name of the schema to which the object belongs. objname indicates the object name. objtype indicates the object type. If the object type is a package, the value is package. If the object type is a function or stored procedure, the value is function.

Example:

This function does not return the invalidation result. You can query the invalidation result in the gs_glc_memory_detail view. If the object is not invalidated, you can find it in valid state in the view. If the object is invalidated, there is no such state.

invalidate_plsql_object belongs to the pg_catalog schema. It can be called even if no schema is specified.

-- Create the package pkg1 and function f2, and query the view cache information. The status is valid.
gaussdb=# CREATE SCHEMA testInvalidate;
CREATE SCHEMA
gaussdb=# SET CURRENT_SCHEMA TO testInvalidate;
SET
gaussdb=# CREATE OR REPLACE PACKAGE pkg1 AS
gaussdb$#     var1 VARCHAR;
gaussdb$# FUNCTION f1() RETURN VARCHAR;
gaussdb$# END pkg1;
gaussdb$# /
CREATE PACKAGE
gaussdb=# CREATE OR REPLACE PACKAGE BODY pkg1 AS
gaussdb$#   FUNCTION f1() RETURN VARCHAR
gaussdb$#   IS
gaussdb$#   BEGIN
gaussdb$#     var1 := '2';
gaussdb$# RETURN var1;
gaussdb$#   END;
gaussdb$# END pkg1;
gaussdb$# /
CREATE PACKAGE BODY
gaussdb=# CREATE OR REPLACE FUNCTION f2() RETURN VARCHAR
gaussdb-# IS
gaussdb$# DECLARE
gaussdb$# var2 VARCHAR := 'li2';
gaussdb$# BEGIN
gaussdb$#   var2='22';
gaussdb$#   RETURN var2;
gaussdb$# END
gaussdb$# /
CREATE FUNCTION
gaussdb=# CALL pkg1.f1();
 f1 
----
 2
(1 row)
gaussdb=# CALL f2();
 f2 
----
 22
(1 row)
gaussdb=# SELECT * FROM gs_glc_memory_detail WHERE schema = 'testinvalidate';
 contextname  | database |     schema     | type | status |       location       | env | usedsize | usecount |  oid  | isspec |           searchpath           | invalidreason 
--------------+----------+----------------+------+--------+----------------------+-----+----------+----------+-------+--------+--------------------------------+---------------
 GLC_PKG_pkg1 | postgres | testinvalidate | pkg  | valid  | in_global_hash_table |   0 |    19856 |        1 | 16755 | f      | addUser: false, schemas: 16754 | valid
 GLC_FUNC_f1  | postgres | testinvalidate | func | valid  | in_global_hash_table |   0 |    10840 |        1 | 16756 |        | addUser: false, schemas: 16754 | valid
 GLC_FUNC_f2  | postgres | testinvalidate | func | valid  | in_global_hash_table |   0 |    12072 |        1 | 16757 |        | addUser: false, schemas: 16754 | valid
(3 rows)
-- Call the function and specify the schema, function name, and type to invalidate pkg1. Query the view again. The valid status line corresponding to pkg1 does not exist.
gaussdb=# SELECT invalidate_plsql_object('testinvalidate','pkg1','package');
 invalidate_plsql_object 
-------------------------
(1 row)
-- To invalidate a function, set the parameters as follows:
gaussdb=# SELECT invalidate_plsql_object('testinvalidate','f2','function');
 invalidate_plsql_object 
-------------------------
(1 row)
-- If there is no input parameter, all cache objects will be invalidated.
gaussdb=# SELECT invalidate_plsql_object();
 invalidate_plsql_object 
-------------------------
(1 row)

gs_plsql_memory_object_detail(db_oid, obj_oid, obj_type)

Description: Obtains the valid memory usage of packages and functions created during stored procedures. When enable_global_plsqlcache is set to off, this function displays the memory usage of stored procedures in the current session. Cross-session query is not supported. The user who calls this function must have the SYSADMIN permission.

Parameters: This function requires the db_oid, obj_oid, and obj_type input parameters. If the input parameters do not match, an empty line is returned. For details about the parameters, see Table 1. If the db_oid and obj_oid input parameters are set to 0, the valid memory usage of all packages and functions in the cache is queried by default.

Return type: tuple

The gs_plsql_memory_object_detail function displays the valid memory usage that meets the query conditions in the database, as described in Table 2.

Example:

-- When enable_global_plsqlcache is set to on, create a package and a function and call gs_plsql_memory_object_detail to obtain the memory usage.

gaussdb=# CREATE OR REPLACE PACKAGE my_package AS
gaussdb$#   PROCEDURE public_proc(a int, b int);
gaussdb$# END my_package;
gaussdb$# /
CREATE PACKAGE
gaussdb=# CREATE OR REPLACE PACKAGE BODY my_package AS
gaussdb$#   PROCEDURE public_proc(a int, b int) AS
gaussdb$#   BEGIN
gaussdb$#     RAISE NOTICE 'a + b = %', a + b;
gaussdb$#   END;
gaussdb$# END my_package;
gaussdb$# /
CREATE PACKAGE BODY
gaussdb=# BEGIN
gaussdb$#   my_package.public_proc(2, 3);
gaussdb$# END;
gaussdb$# /
NOTICE:  a + b = 5
CONTEXT:  SQL statement "CALL my_package.public_proc(2,3)"
PL/pgSQL function inline_code_block line 2 at PERFORM
ANONYMOUS BLOCK EXECUTE
gaussdb=# SELECT * FROM gs_plsql_memory_object_detail(0,16716,'pkg');
 object_oid |    context_name    |    item     |          searchpath           |       guc       |      file      | line | size | db_oid 
------------+--------------------+-------------+-------------------------------+-----------------+----------------+------+------+--------
      16716 | GLC_PKG_my_package | pkg         | addUser: false, schemas: 2200 | 303465209266176 | pl_package.cpp | 1143 | 1096 |  14584
      16716 | GLC_PKG_my_package | invalItems  | addUser: false, schemas: 2200 | 303465209266176 | NA             |    0 |    0 |  14584
      16716 | GLC_PKG_my_package | proc_list   | addUser: false, schemas: 2200 | 303465209266176 | list.cpp       |  110 | 3168 |  14584
      16716 | GLC_PKG_my_package | ndatums     | addUser: false, schemas: 2200 | 303465209266176 | pl_package.cpp | 1342 |    8 |  14584
      16716 | GLC_PKG_my_package | GSPLSQLType | addUser: false, schemas: 2200 | 303465209266176 | NA             |    0 |    0 |  14584
      16716 | GLC_PKG_my_package | namespace   | addUser: false, schemas: 2200 | 303465209266176 | pl_funcs.cpp   |  229 |  240 |  14584
(6 rows)
gaussdb=# SELECT * FROM gs_plsql_memory_object_detail(0,16717,'func');
 object_oid |     context_name     |    item    |          searchpath           |       guc       |     file     | line | size | db_oid 
------------+----------------------+------------+-------------------------------+-----------------+--------------+------+------+--------
      16717 | GLC_FUNC_public_proc | function   | addUser: false, schemas: 2200 | 303465209266176 | pl_comp.cpp  | 1538 | 1800 |  14584
      16717 | GLC_FUNC_public_proc | ndatums    | addUser: false, schemas: 2200 | 303465209266176 | pl_comp.cpp  | 2362 | 6072 |  14584
      16717 | GLC_FUNC_public_proc | invalItems | addUser: false, schemas: 2200 | 303465209266176 | NA           |    0 |    0 |  14584
      16717 | GLC_FUNC_public_proc | exprlist   | addUser: false, schemas: 2200 | 303465209266176 | list.cpp     |  110 |  320 |  14584
      16717 | GLC_FUNC_public_proc | StmtBlock  | addUser: false, schemas: 2200 | 303465209266176 | pl_gram.cpp  |  696 |  176 |  14584
      16717 | GLC_FUNC_public_proc | namespace  | addUser: false, schemas: 2200 | 303465209266176 | pl_funcs.cpp |  229 | 1152 |  14584
(6 rows)