Foreword
This article introduces the cross-transaction use cases of cursors in PLSQL and SQL layers, and a case study.
Verified version of this article:
db0=# select version();
db0-#/
version
----------------------------------------
KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
One, a cursor in a PLSQL block spans transactions
Native plpgsql cross-transaction example, cross-transaction is not supported
drop table t2;
create table t2(id int,name varchar(20));
insert into t2 values (1,'aaa'), (2,'bbb'), (3,'ccc');
create or replace procedure testcur02
as
$$
declare
cur1 refcursor;
vid numeric;
vname varchar(20);
begin
open cur1 for select * from t2;
commit;
fetch cur1 into vid,vname; --Because cur1 is automatically released after commit, the error will not exist
raise notice 'vid:%,vname:%',vid,vname;
close cur1;
end;
$$ language plpgsql;
call testcur02();
--operation result
db0=# call testcur02();
db0-#
db0-#/
error: cursor "<unnamed portal 8>" does not exist
context: pl/pgsql function testcur02() line 9 at fetch
kes-plsql supports cross-transactions in stored procedures by default
drop table t2;
create table t2(id int,name varchar(20));
insert into t2 values (1,'aaa'), (2,'bbb'), (3,'ccc');
create or replace procedure testcur01
is
declare
cur1 refcursor;
vid numeric;
vname varchar(20);
begin
open cur1 for select * from t2;
commit;
fetch cur1 into vid,vname; --cur1 will not be automatically released after commit
raise notice 'vid:%,vname:%',vid,vname;
close cur1;
end;
call testcur01();
--operation result
db0=# call testcur01();
db0-# /
notice: vid:1,vname:aaa
call
db0=#
Second, SQL layer cursors and transactions
The SQL layer also has cursor statements, and cursors can also be declared. In order to verify cross-transaction issues, take the fetch all in statement as an example.
create or replace procedure testcur04(pcur1 out refcursor)
is
begin
open pcur1 for select * from t2;
end;
db0=# begin; ---Start transaction
db0-#/
BEGIN
db0=# call testcur04('vcur'); ----Get the cursor result set
db0-#/
pcur1
-------
vcur
(1 row)
db0=# fetch all in vcur; ---Fetch the cursor result set at one time
db0-# /
id|name
---- + ------
1|aaa
2 | bbb
3|ccc
(3 rows)
db0=#
In the earlier V7 version, you can turn on the enable_out_refcursor_holdable parameter to on.
Implement the above cursor result set across transactions at the SQL layer (requiring the parameter mode of the stored procedure to be out or inout). According to current measurements, V008R006C007B0024 no longer supports this parameter.
Three, one case of cursors across transactions
This is a problem that occurs on the V7 version.
Problem description: The customer application layer code uses fetch al in to obtain the cursor result set, but an error is reported that the cursor does not exist. The complete intercepted customer code is as follows;
[2023-07-07 09:56:20.605 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: SELECT nspname, proname, proretset, prorettypedef , Pronargs, ProargTypes, Protype, Proargnames, Proargmodes, ProargTypmods from Sys_Catalog.Syspace Join SYS_CATALOG.SYS_PROC On SYS_PROC.PR.PR Onamespace = Sys_namespace.oid and (Not ProreTSet) and NSPNAME NOT LIKE ‘SYS_%’ Escape ” And NSPNAME! = ‘Information_SChema ‘ LEFT JOIN sys_catalog.sys_package ON sys_proc.propkgoid = sys_package.oid AND sys_package.pkgnamespace = sys_namespace.oid WHERE 1 AND proname LIKE ‘PROC_KBV7_OCI_0X1794F90’ ESCAPE ” ORDER BY nspname, proname, proretset
—First paragraph in boldface
[2023-07-07 09:56:20.607 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: SET ENABLE_OUT_REFCURSOR_HOLDABLE TO on
[2023-07-07 09:56:20.608 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: call PROC_KBV7_OCI_0x1794f90(‘KDBCUR_178A2F0_0 ‘)
[2023-07-07 09:56:20.609 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: fetch all in KDBCUR_178A2F0_0
[2023-07-07 09:56:20.609 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: drop procedure if exists PROC_KBV7_OCI_0x1794f90(REFCURSOR)
[2023-07-07 09:56:20.611 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: close KDBCUR_178A2F0_0
[2023-07-07 09:56:20.613 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: parse _PLAN0X178D4B0: select object_name from user_objects where object_name = ‘TRIGGER_PKG’ and (object_type = ‘PACKAGE’ or object_type = ‘PACKAGE BODY’) and status<>‘VALID’
[2023-07-07 09:56:20.616 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: EXECUTE _PLAN0x178d4b0()
[2023-07-07 09:56:20.616 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]DETAIL: prepare: select object_name from user_objects where object_name = ‘ TRIGGER_PKG’ and (object_type = ‘PACKAGE’ or object_type = ‘PACKAGE BODY’) and status<>‘VALID’
[2023-07-07 09:56:20.623 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: close _PLAN0X178D4B0
[2023-07-07 09:56:20.624 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: SELECT PRONAME, PROTYPE FROM SYS_PROC, SYS_PACKAGE WHERE SYS_PROC.PROPKGOID = SYS_PACKAGE.OID AND PKGNAME = UPPER(‘TRIGGER_PKG’);
[2023-07-07 09:56:20.626 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: SELECT nspname, proname, proretset, prorettypedef , Pronargs, ProargTypes, Protype, Proargnames, Proargmodes, ProargTypmods from Sys_Catalog.Syspace Join SYS_CATALOG.SYS_PROC On SYS_PROC.PR.PR Onamespace = Sys_namespace.oid and (Not ProreTSet) and NSPNAME NOT LIKE ‘SYS_%’ Escape ” And NSPNAME! = ‘Information_SChema ‘ LEFT JOIN sys_catalog.sys_package ON sys_proc.propkgoid = sys_package.oid AND sys_package.pkgnamespace = sys_namespace.oid WHERE 1 AND pkgname LIKE UPPER(‘TRIGGER_PKG’) ESCAPE ” AND proname LIKE UPPER(‘SELECT_TRIGGER_ACTIONS’) ESCAPE ” OR BY nspname, proname, proretset
[2023-07-07 09:56:20.628 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: CREATE OR REPLACE PROCEDURE PROC_KBV7_OCI_0x1786320(INTERNAL_ PREFIX_0 INOUT REFCURSOR)
AS
begin TRIGGER_PKG.select_trigger_actions(INTERNAL_PREFIX_0); end;
[2023-07-07 09:56:20.631 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: SELECT nspname, proname, proretset, prorettypedef , Pronargs, ProargTypes, Protype, Proargnames, Proargmodes, ProargTypmods from Sys_Catalog.Syspace Join SYS_CATALOG.SYS_PROC On SYS_PROC.PR.PR Onamespace = Sys_namespace.oid and (Not ProreTSet) and NSPNAME NOT LIKE ‘SYS_%’ Escape ” And NSPNAME! = ‘Information_SChema ‘ LEFT JOIN sys_catalog.sys_package ON sys_proc.propkgoid = sys_package.oid AND sys_package.pkgnamespace = sys_namespace.oid WHERE 1 AND proname LIKE ‘PROC_KBV7_OCI_0X1786320’ ESCAPE ” ORDER BY nspname, proname, proretset
—Second paragraph in boldface
[2023-07-07 09:56:20.633 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: call PROC_KBV7_OCI_0x1786320(‘KDBCUR_178902 0_1 ‘)
[2023-07-07 09:56:20.634 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: fetch all in KDBCUR_1789020_1
2023-07-07 09:56:20.634 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]ERROR: cursor “KDBCUR_1789020_1” does not exist
[2023-07-07 09:56:20.634 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]STATEMENT: fetch all in KDBCUR_1789020_1
[2023-07-07 09:56:20.635 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: drop procedure if exists PROC_KBV7_OCI_0x1786320(REFCURSOR)
[2023-07-07 09:56:20.636 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]LOG: statement: close KDBCUR_1789020_1
[2023-07-07 09:56:20.636 CST] [192.1.153.142][192.1.153.142(49637)][EMS][34][D5000][0]ERROR: cursor “KDBCUR_1789020_1” does not exist
As can be seen from the bold log in the first paragraph above, SET ENABLE_OUT_REFCURSOR_HOLDABLE TO on enables the cursor to be executed across transactions, and the fetch all in KDBCUR_178A2F0_0 cursor extraction is successful, but the same operation in the second log produces the error “cursor” KDBCUR_1789020_1″ does not exist” problem.
Actual measurement by writing a reduced case by yourself:
create or replace procedure proc_test01 (
pcur inout refcursor
)
as
begin
open for select * from t1;
end ;
SET ENABLE_OUT_REFCURSOR_HOLDABLE TO on;
CALL PROC_TEST01('KDBCUR')
fetch all in KDBCUR
The above code can obtain the result set, but the parameter of proc_test01 must be in inout or out mode. Finally, by tracing the definition of the customer’s PROC_KBV7_OCI_0x1786320 stored procedure, we found that it did not position the parameter mode as inout or out mode, but IN mode. So the problem is found.
Kingbase ES cursor cross-transaction and its case