【测试类型：SQL功能】【测试版本：5.1.0】存储过程在会话级内存泄露

【标题描述】:存储过程在会话级内存泄露
【测试类型：SQL功能】【测试版本：5.1.0】存储过程在会话级内存泄露
【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
openEuler release 20.03 (LTS-SP1)
Linux kwepwebenv15397 4.19.90-2012.5.0.0054.oe1.x86_64 #1 SMP Tue Dec 22 15:58:47 UTC 2020 x86_64
x86_64 x86_64 GNU/Linux
【测试环境】(单机/1主x备x级联备):
1主1备
【被测功能】:
存储过程
【测试类型】:
功能测试
【数据库版本】(查询命令: gaussdb -V):
gaussdb (openGauss 5.0.0 build 57765fee) compiled at 2023-03-30 03:07:39 commit 0 last mr
【预置条件】:
数据库正常运行
【操作步骤】(请填写详细的操作步骤):

建表
create table test1(col1 int, col2 int);
创建存储过程
CREATE OR REPLACE PROCEDURE estTransaction(
i_val1 IN numeric,
i_val2 IN varchar,
o_val OUT INT
)
AS DECLARE
pragma autonomous_transaction;
BEGIN
o_val := i_val1 + i_val2;
insert into test1(col1, col2) values(i_val1, i_val2);
commit;
END;
/
3.会话1循环调用存储过程
BEGIN
for i in 1 .. 1000000 loop
PERFORM estTransaction(i,i+1);
END loop;
END;
/
4.会话2在会话1执行的同时执行查询操作，分析查询结果中usedsize是否增长
select * from gs_session_memory_detail where contextname='OptimizerTopMemoryContext'
【预期输出】:
1.成功
2.成功
3.成功
4.字段usedsize值不变
【实际输出】:
字段usedsize值在step3执行过程中一直在增长，终止step3，usedsize值不再增长。
【原因分析】:
这个问题的根因
问题推断过程
还有哪些原因可能造成类似现象
该问题是否有临时规避措施
问题解决方案
预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

【测试代码】:

Hi @chengjiaqi, please use the command /sig xxx to add a SIG label to this issue.
For example: /sig sqlengine or /sig storageengine or /sig om or /sig ai and so on.
You can find more SIG labels from Here.
If you have no idea about that, please contact with @xiangxinyong , @zhangxubo .

Hey @chengjiaqi, Welcome to openGauss Community.
All of the projects in openGauss Community are maintained by @opengauss-bot.
That means the developers can comment below every pull request or issue to trigger Bot Commands.
Please follow instructions at Here to find the details.

会话一调用存储过程的同时，会话二查询无内存泄露，usedsize值不变。
输入图片说明

输入图片说明

5.0.1验收通过
gaussdb (openGauss 5.0.1 build 1afdf2b0) compiled at 2023-10-12 17:53:57 commit 0 last mr

会话1：

test=# create table test1(col1 int, col2 int);
CREATE TABLE
test=# CREATE OR REPLACE PROCEDURE estTransaction(
test(# i_val1 IN numeric,
test(# i_val2 IN varchar,
test(# o_val OUT INT
test(# )
test-# AS DECLARE
test$# pragma autonomous_transaction;
test$# BEGIN
test$# o_val := i_val1 + i_val2;
test$# insert into test1(col1, col2) values(i_val1, i_val2);
test$# commit;
test$# END;
test$# /
CREATE PROCEDURE
test=#
test=# BEGIN
test$# for i in 1 .. 1000000 loop
test$# PERFORM estTransaction(i,i+1);
test$# END loop;
test$# END;
test$# /

会话2：

test=# select * from gs_session_memory_detail where contextname='OptimizerTopMemoryContext';
           sessid           |        sesstype        |        contextname        | level |         parent         | totalsize | freesize | usedsize
----------------------------+------------------------+---------------------------+-------+------------------------+-----------+----------+----------
 0.139711248846848          | postmaster             | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706446051072 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706494338816 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706527901440 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706544682752 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706696201984 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706756622080 | auditor                | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706773403392 | Statement flush thread | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706816460544 | ASP                    | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706849548032 | getpercentile          | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706866329344 | CfsShrinker            | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706883110656 | TxnSnapCapturer        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706903099136 | PgstatCollector        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706927806208 | ApplyLauncher          | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706969290496 | StatsTracker           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706999760640 | UndoLauncher           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707016541952 | JobScheduler           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139707055339264 | AutoVacLauncher        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707089417984 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707117729536 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707146036992 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707162818304 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707193227008 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707218392832 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707239810816 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707256592128 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445764.139706511120128 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445766.139706421868288 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697446260.139706209924864 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     74832 |     5416 |    69416
 1697446405.139706164770560 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     41512 |     5480 |    36032
 1697446457.139706115421952 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     41512 |     5480 |    36032
(31 rows)
test=# select * from gs_session_memory_detail where contextname='OptimizerTopMemoryContext';
           sessid           |        sesstype        |        contextname        | level |         parent         | totalsize | freesize | usedsize
----------------------------+------------------------+---------------------------+-------+------------------------+-----------+----------+----------
 0.139711248846848          | postmaster             | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706446051072 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706494338816 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706527901440 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706544682752 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706696201984 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706756622080 | auditor                | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706773403392 | Statement flush thread | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706816460544 | ASP                    | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706849548032 | getpercentile          | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706866329344 | CfsShrinker            | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706883110656 | TxnSnapCapturer        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706903099136 | PgstatCollector        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706927806208 | ApplyLauncher          | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706969290496 | StatsTracker           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706999760640 | UndoLauncher           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707016541952 | JobScheduler           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139707055339264 | AutoVacLauncher        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707089417984 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707117729536 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707146036992 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707162818304 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707193227008 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707218392832 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707239810816 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707256592128 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445764.139706511120128 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445766.139706421868288 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697446260.139706209924864 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     74832 |     5416 |    69416
 1697446405.139706164770560 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     41512 |     5480 |    36032
 1697446457.139706115421952 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     41512 |     5480 |    36032
(31 rows)
test=# select * from gs_session_memory_detail where contextname='OptimizerTopMemoryContext';
           sessid           |        sesstype        |        contextname        | level |         parent         | totalsize | freesize | usedsize
----------------------------+------------------------+---------------------------+-------+------------------------+-----------+----------+----------
 0.139711248846848          | postmaster             | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706446051072 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706494338816 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706527901440 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706544682752 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706696201984 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706756622080 | auditor                | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706773403392 | Statement flush thread | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706816460544 | ASP                    | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706849548032 | getpercentile          | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706866329344 | CfsShrinker            | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706883110656 | TxnSnapCapturer        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706903099136 | PgstatCollector        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706927806208 | ApplyLauncher          | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139706969290496 | StatsTracker           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139706999760640 | UndoLauncher           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707016541952 | JobScheduler           | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7752 |      440
 1697445762.139707055339264 | AutoVacLauncher        | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707089417984 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707117729536 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707146036992 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707162818304 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707193227008 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707218392832 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707239810816 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445762.139707256592128 | Auxiliary              | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445764.139706511120128 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697445766.139706421868288 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |      8192 |     7792 |      400
 1697446260.139706209924864 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     74832 |     5416 |    69416
 1697446405.139706164770560 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     41512 |     5480 |    36032
 1697446457.139706115421952 | postgres               | OptimizerTopMemoryContext |     1 | ThreadTopMemoryContext |     41512 |     5480 |    36032
(31 rows)

GVP openGauss / openGauss-server

内容风险标识

评论 (3)

GVPopenGauss / openGauss-server

内容风险标识