【测试类型：SQL功能】【测试版本：7.0.0-RC1】 timescaledb时序表查看SQL执行计划数据库挂掉

【标题描述】:  timescaledb时序表查看SQL执行计划数据库挂掉
【测试类型：SQL功能】【测试版本：7.0.0-RC1】 问题描述： timescaledb时序表查看SQL执行计划数据库挂掉

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
openEuler release 22.03 LTS
Linux openeuler-22 5.10.0-60.139.0.166.oe2203.x86_64 #1 SMP Thu May 30 05:17:57 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

【测试环境】(单机/1主x备x级联备):
单机

【被测功能】:
timescaledb插件

【测试类型】:
功能测试

【数据库版本】(查询命令: gaussdb -V):
gaussdb (openGauss 6.0.0 build 798b1578) compiled at 2024-09-30 00:45:04 commit 0 last mr

【预置条件】:
timescaledb插件加载成功

【操作步骤】(请填写详细的操作步骤):
1. 创建时序表并写入数据

```
CREATE TABLE PUBLIC."two_Partitions" (
  "timeCustom" BIGINT NOT NULL,
  device_id TEXT NOT NULL,
  series_0 DOUBLE PRECISION NULL,
  series_1 DOUBLE PRECISION NULL,
  series_2 DOUBLE PRECISION NULL,
  series_bool BOOLEAN NULL
);
CREATE INDEX ON PUBLIC."two_Partitions" (device_id, "timeCustom" DESC NULLS LAST) WHERE device_id IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_0) WHERE series_0 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_1)  WHERE series_1 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_2) WHERE series_2 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_bool) WHERE series_bool IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, device_id);
SELECT * FROM create_hypertable('"public"."two_Partitions"'::regclass, 'timeCustom'::name, 'device_id'::name, associated_schema_name=>'_timescaledb_internal'::text, number_partitions => 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
BEGIN;
\COPY public."two_Partitions" FROM 'data/ds1_dev1_1.tsv' NULL AS '';
COMMIT;
INSERT INTO public."two_Partitions"("timeCustom", device_id, series_0, series_1) VALUES
(1257987600000000000, 'dev1', 1.5, 1),
(1257987600000000000, 'dev1', 1.5, 2),
(1257894000000000000, 'dev2', 1.5, 1),
(1257894002000000000, 'dev1', 2.5, 3);
INSERT INTO "two_Partitions"("timeCustom", device_id, series_0, series_1) VALUES
(1257894000000000000, 'dev2', 1.5, 2);
```

2. 查看SQL执行计划
```
EXPLAIN (verbose ON, costs off)SELECT "timeCustom" t, min(series_0) FROM PUBLIC."two_Partitions" GROUP BY t ORDER BY t DESC NULLS LAST limit 2; 
```
3. 数据预置

```
CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
    COST 0.0000001 LANGUAGE plpgsql
    AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
CREATE USER regress_rls_bob identified by 'qwer*963.' NOLOGIN;
CREATE USER regress_rls_carol identified by 'qwer*963.' NOLOGIN;
CREATE USER regress_rls_alice identified by 'qwer*963.' NOLOGIN;
GRANT CREATE ON SCHEMA public TO public;
SET SESSION AUTHORIZATION regress_rls_alice password 'qwer*963.' ;
CREATE TABLE b1 (a int, b text);
SELECT public.create_hypertable('b1', 'a', chunk_time_interval=>2);
INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
CREATE POLICY p1 ON b1 USING (a % 2 = 0);
ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
GRANT ALL ON b1 TO regress_rls_bob;
SET SESSION AUTHORIZATION regress_rls_bob password 'qwer*963.' ;
CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
GRANT ALL ON bv1 TO regress_rls_carol;
```

4. 查看执行计划

```
SET SESSION AUTHORIZATION regress_rls_carol password 'qwer*963.' ;
EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);  
```

【预期输出】:
1. 创建时序表并写入数据成功
2. 查看执行计划如下
![输入图片说明](https://foruda.gitee.com/images/1728725125463696226/9a110c35_10825457.png "屏幕截图")
3. 数据预置成功
4. 查看执行计划成功

【实际输出】:
1. 创建时序表并写入数据成功
2. 查看执行计划如下
![输入图片说明](https://foruda.gitee.com/images/1728725142528853063/3a8734d9_10825457.png "屏幕截图")
3. 数据预置成功
4. 查看执行计划失败
![输入图片说明](https://foruda.gitee.com/images/1729149203854227549/f5634361_10825457.png "屏幕截图")

【原因分析】:
1. 这个问题的根因
2. 问题推断过程
3. 还有哪些原因可能造成类似现象
4. 该问题是否有临时规避措施
5. 问题解决方案
6. 预计修复问题时间

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

【测试代码】:

openGauss/Plugin

内容风险标识

评论 (4)

openGauss/Plugin .gitee-modal { width: 500px !important; }

内容风险标识