[Bug]: Online 方式设置列的非 null 属性时时延增加一倍-openGauss-server-AtomGit

已关闭

[Bug]: Online 方式设置列的非 null 属性时时延增加一倍 #8372

TonsnakeLin创建于 8月4日关闭于 21 天前

TonsnakeLin

8月4日创建

测试类型

性能

测试版本

7.0.0LTS

问题描述

CREATE TABLE student (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INTEGER -- 允许 NULL
);
插入2亿条数据
ALTER TABLE concurrently student ALTER COLUMN age SET NOT NULL;
预计耗时35秒，实际耗时70秒

操作系统和硬件信息

Linux linpin-PC 4.19.0-amd64-desktop #5444 SMP Wed Jun 24 15:22:46 CST 2026 x86_64 GNU/Linux

测试环境

企业版单机

被测功能

ALTER TABLE concurrently student ALTER COLUMN age SET NOT NULL;

预置条件

操作步骤

创建表
CREATE TABLE student (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INTEGER -- 允许 NULL
);
插入2亿条数据
session1 执行 online ddl
ALTER TABLE concurrently student ALTER COLUMN age SET NOT NULL;
session2 执行 insert
INSERT INTO "student" (name, age)
SELECT
' 小明_' || s AS name,
18 + (s % 50) AS age
FROM generate_series (1, 10000000) s;

预期输出

执行ALTER TABLE concurrently student ALTER COLUMN age SET NOT NULL 耗时与 ALTER TABLE student ALTER COLUMN age SET NOT NULL 耗时接近。

实际输出

执行ALTER TABLE concurrently student ALTER COLUMN age SET NOT NULL 耗时70秒，ALTER TABLE student ALTER COLUMN age SET NOT NULL 耗时35秒。

日志信息

无

提单组织

社区用户

测试代码

opengauss_bot成员

8月4日评论：

This issue requires an assignee. Since you haven't specified one, we've assigned TestManager as the default assignee for this issue.

opengauss_bot成员

8月4日将 TestManager 设为负责人

opengauss_bot成员

8月4日添加了label：sig/StorageEngine,sig/AI,sig/CM,sig/CloudNative,sig/SecurityTechnology,sig/SQLEngine

opengauss_bot成员

8月4日评论：

Welcome To openGauss Community

Hey @m0_58153217 , thanks for your contribution to the community.

Bot Usage Manual

I'm the Bot here serving you. You can find the instructions on how to interact with me at Here . That means you can comment below every pull request or issue to trigger Bot Commands.

Contact Guide

If you have any questions, please contact the SIG: StorageEngine, AI, CM, CloudNative, SecurityTechnology, SQLEngine ,
and any of the maintainers: @CarrotGo, @chendong76, @chenxiaobin19, @congzhou2603, @dodders, @hwworkholic, @jemappellehc, @muyulinzhong, @quemingjian, @shenzheng4, @shirley_zhengx, @superlchf, @totaj, @wlff234, @wofanzheng, @ywzq1161327784 ,
and any of the committers: @Igali, @bihua111, @cailei19, @h_ray, @levy53071, @libiao2024, @lihaixiao, @mrzack, @wangfeihuo, @wuyuechuan, @xiong_xjun, @zhangfengzhi123, @zhangxubo, @zhangzq131, @zjh_hw .

sungang14成员

8月6日 issue优先级由无优先级改变为次要

sungang14成员

8月6日关联了看板：openGauss 7.0.0-LTS

ywzq1161327784成员

8月7日将 congzhou2603 设为负责人

muyulinzhong成员

8月10日移除了负责人 TestManager

zhoucong<Okidoki>成员

8月26日评论：

[lzf_cm@host2 ~]$ source /home/lzf_cm_install/ENVFILE
[lzf_cm@host2 ~]$ cm_ctl query -Cvipd
[  CMServer State   ]

node     node_ip         instance                                       state
-------------------------------------------------------------------------------
1  host1 20.20.20.22      1    /home/lzf_cm_install/install/cm/cm_server Primary
2  host2 20.20.20.32      2    /home/lzf_cm_install/install/cm/cm_server Standby

[   Cluster State   ]

cluster_state   : Normal
redistributing  : No
balanced        : No
current_az      : AZ_ALL

[  Datanode State   ]

node     node_ip         instance                                         state            | node     node_ip         instance                                         state
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1  host1 20.20.20.22      6001 11000  /home/lzf_cm_install/install/data/dn P Standby Normal | 2  host2 20.20.20.32      6002 11000  /home/lzf_cm_install/install/data/dn S Primary Normal
[lzf_cm@host2 ~]$ gsql -r
gsql ((openGauss 7.0.0 build 93bfec54) compiled at 2026-08-26 10:13:12 commit 0 last mr 9389 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# -- ==========================================================================
--
-- 可以直接执行整个文件：
--   gsql -d postgres -p 5432 \
--     -f online_ddl_tidrange_manual_tests/01_case1_perf_compare_simple.sql
--
-- 默认每张表 500 万行。完整复现问题单可将 5000000 改为 200000000。
-- ==========================================================================

DROP SCHEMA IF EXISTS online_ddl_perf_compare CASCADE;
CREATE SCHEMA online_ddl_perf_compare;

-- 普通 ALTER TABLE 测试表。
CREATE TABLE online_ddl_perf_compare.student_offline
(
    id   bigint NOT NULL,
    name varchar(50),
    age  integer
);

openGauss=# -- CASE 01 简化版：普通 ALTER TABLE 与 online ALTER TABLE 性能对比
openGauss=# --
openGauss=# -- 可以直接执行整个文件：
openGauss=# --   gsql -d postgres -p 5432 \
openGauss=# --     -f online_ddl_tidrange_manual_tests/01_case1_perf_compare_simple.sql
openGauss=# --
openGauss=# -- 默认每张表 500 万行。完整复现问题单可将 5000000 改为 200000000。
openGauss=# -- ==========================================================================
openGauss=#
openGauss=# DROP SCHEMA IF EXISTS online_ddl_perf_compare CASCADE;
NOTICE:  schema "online_ddl_perf_compare" does not exist, skipping
DROP SCHEMA
    ON online_ddl_perf_compare.student_offline(id);

CREATE UNIQUE INDEX student_online_id_uq
    ON online_ddl_perf_compare.student_online(id);

INSERT INTO online_ddl_perf_compare.student_offline
SELECT g, 'student_' || g, 18 + (g % 50)
openGauss=# CREATE SCHEMA online_ddl_perf_compare;
  FROM generate_series(1, 5000000) g;

INSERT INTO online_ddl_perf_compare.student_online
SELECT g, 'student_' || g, 18 + (g % 50)
  FROM generate_series(1, 5000000) g;

ANALYZE online_ddl_perf_compare.student_offline;
ANALYZE online_ddl_perf_compare.student_online;

SELECT 'ordinary' AS test_mode, count(*) AS rows
  FROM online_ddl_perf_compare.student_offline
UNION ALL
SELECT 'online' AS test_mode, count(*) AS rows
  FROM online_ddl_perf_compare.student_online
 ORDER BY test_mode;

-- 重新连接，避免数据准备阶段影响 DDL 计时。
\c :DBNAME

CHECKPOINT;

\timing on
SET client_min_messages = notice;
SET statement_timeout = 0;

SELECT 'ordinary ALTER TABLE begin' AS test_step,
       clock_timestamp() AS event_time;

ALTER TABLE online_ddl_perf_compare.student_offline
    ALTER COLUMN age SET NOT NULL;

SELECT 'ordinary ALTER TABLE end' AS test_step,
       clock_timestamp() AS event_time;

SELECT 'online ALTER TABLE begin' AS test_step,
       clock_timestamp() AS event_time;

ALTER TABLE CONCURRENTLY online_ddl_perf_compare.student_online
    ALTER COLUMN age SET NOT NULL;

SELECT 'online ALTER TABLE end' AS test_step,
       clock_timestamp() AS event_time;

\timing off

-- 精确行数校验。不要使用 pg_class.reltuples，它只是统计估算值。
SELECT 'ordinary' AS test_mode,
       count(*) AS actual_rows,
       count(*) FILTER (WHERE age IS NULL) AS null_rows
  FROM online_ddl_perf_compare.student_offline
UNION ALL
SELECT 'online' AS test_mode,
CREATE SCHEMA
openGauss=#
openGauss=# -- 普通 ALTER TABLE 测试表。
openGauss=# CREATE TABLE online_ddl_perf_compare.student_offline
openGauss-# (
openGauss(#     id   bigint NOT NULL,
openGauss(#     name varchar(50),
openGauss(#     age  integer
openGauss(# );
 WHERE n.nspname = 'online_ddl_perf_compare'
   AND c.relname IN ('student_offline', 'student_online')
   AND a.attname = 'age'
 ORDER BY c.relname;

-- 测试完成后按需清理：
-- DROP SCHEMA online_ddl_perf_compare CASCADE;

CREATE TABLE
openGauss=#
openGauss=# -- online ALTER TABLE 测试表。
openGauss=# CREATE TABLE online_ddl_perf_compare.student_online
openGauss-# (
openGauss(#     id   bigint NOT NULL,
openGauss(#     name varchar(50),
openGauss(#     age  integer
openGauss(# );
CREATE TABLE
openGauss=#
openGauss=# CREATE UNIQUE INDEX student_offline_id_uq
openGauss-#     ON online_ddl_perf_compare.student_offline(id);
CREATE INDEX
openGauss=#
openGauss=# CREATE UNIQUE INDEX student_online_id_uq
openGauss-#     ON online_ddl_perf_compare.student_online(id);
CREATE INDEX
openGauss=#
openGauss=# INSERT INTO online_ddl_perf_compare.student_offline
openGauss-# SELECT g, 'student_' || g, 18 + (g % 50)
openGauss-#   FROM generate_series(1, 5000000) g;
INSERT 0 5000000
openGauss=#
openGauss=# INSERT INTO online_ddl_perf_compare.student_online
openGauss-# SELECT g, 'student_' || g, 18 + (g % 50)
openGauss-#   FROM generate_series(1, 5000000) g;
INSERT 0 5000000
openGauss=#
openGauss=# ANALYZE online_ddl_perf_compare.student_offline;
ANALYZE
openGauss=# ANALYZE online_ddl_perf_compare.student_online;
ANALYZE
openGauss=#
openGauss=# SELECT 'ordinary' AS test_mode, count(*) AS rows
openGauss-#   FROM online_ddl_perf_compare.student_offline
openGauss-# UNION ALL
openGauss-# SELECT 'online' AS test_mode, count(*) AS rows
openGauss-#   FROM online_ddl_perf_compare.student_online
openGauss-#  ORDER BY test_mode;
 test_mode |  rows
-----------+---------
 online    | 5000000
 ordinary  | 5000000
(2 rows)

openGauss=#
openGauss=# -- 重新连接，避免数据准备阶段影响 DDL 计时。
openGauss=# \c :DBNAME
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "lzf_cm".
openGauss=#
openGauss=# CHECKPOINT;
CHECKPOINT
openGauss=#
openGauss=# \timing on
Timing is on.
openGauss=# SET client_min_messages = notice;
SET
Time: 0.246 ms
openGauss=# SET statement_timeout = 0;
SET
Time: 0.124 ms
openGauss=#
openGauss=# SELECT 'ordinary ALTER TABLE begin' AS test_step,
openGauss-#        clock_timestamp() AS event_time;
         test_step          |          event_time
----------------------------+-------------------------------
 ordinary ALTER TABLE begin | 2026-08-26 19:59:17.387706+08
(1 row)

Time: 0.563 ms
openGauss=#
openGauss=# ALTER TABLE online_ddl_perf_compare.student_offline
openGauss-#     ALTER COLUMN age SET NOT NULL;
ALTER TABLE
Time: 931.894 ms
openGauss=#
openGauss=# SELECT 'ordinary ALTER TABLE end' AS test_step,
openGauss-#        clock_timestamp() AS event_time;
        test_step         |          event_time
--------------------------+-------------------------------
 ordinary ALTER TABLE end | 2026-08-26 19:59:18.320603+08
(1 row)

Time: 0.475 ms
openGauss=#
openGauss=# SELECT 'online ALTER TABLE begin' AS test_step,
openGauss-#        clock_timestamp() AS event_time;
        test_step         |          event_time
--------------------------+------------------------------
 online ALTER TABLE begin | 2026-08-26 19:59:18.32105+08
(1 row)

Time: 0.174 ms
openGauss=#
openGauss=# ALTER TABLE CONCURRENTLY online_ddl_perf_compare.student_online
openGauss-#     ALTER COLUMN age SET NOT NULL;
NOTICE:  Online DDL instance init finish, start to copy baseline data.
NOTICE:  Online DDL rewrite catalogs finish, start to copy baseline data.
NOTICE:  Online DDL baseline data check completed, start to check incremental data.
NOTICE:  Online DDL relation student_online get AccessExclusiveLock before commit, start to append for the last time.
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table online_ddl_temp_schema_57500_1663_16374_16391.online_ddl_delta_log
drop cascades to table online_ddl_temp_schema_57500_1663_16374_16391.ctid_map
ALTER TABLE
Time: 682.703 ms
openGauss=#
openGauss=# SELECT 'online ALTER TABLE end' AS test_step,
openGauss-#        clock_timestamp() AS event_time;
       test_step        |          event_time
------------------------+-------------------------------
 online ALTER TABLE end | 2026-08-26 19:59:19.004673+08
(1 row)

Time: 0.414 ms
openGauss=#
openGauss=# \timing off
Timing is off.
openGauss=#
openGauss=# -- 精确行数校验。不要使用 pg_class.reltuples，它只是统计估算值。
openGauss=# SELECT 'ordinary' AS test_mode,
openGauss-#        count(*) AS actual_rows,
openGauss-#        count(*) FILTER (WHERE age IS NULL) AS null_rows
openGauss-#   FROM online_ddl_perf_compare.student_offline
openGauss-# UNION ALL
openGauss-# SELECT 'online' AS test_mode,
openGauss-#        count(*) AS actual_rows,
openGauss-#        count(*) FILTER (WHERE age IS NULL) AS null_rows
openGauss-#   FROM online_ddl_perf_compare.student_online
openGauss-#  ORDER BY test_mode;
 test_mode | actual_rows | null_rows
-----------+-------------+-----------
 online    |     5000000 |         0
 ordinary  |     5000000 |         0
(2 rows)

openGauss=#
openGauss=# SELECT c.relname AS table_name,
openGauss-#        a.attnotnull
openGauss-#   FROM pg_class c
openGauss-#   JOIN pg_namespace n ON n.oid = c.relnamespace
openGauss-#   JOIN pg_attribute a ON a.attrelid = c.oid
openGauss-#  WHERE n.nspname = 'online_ddl_perf_compare'
openGauss-#    AND c.relname IN ('student_offline', 'student_online')
openGauss-#    AND a.attname = 'age'
openGauss-#  ORDER BY c.relname;
   table_name    | attnotnull
-----------------+------------
 student_offline | t
 student_online  | t
(2 rows)

openGauss=#
openGauss=# -- 测试完成后按需清理：
openGauss=# -- DROP SCHEMA online_ddl_perf_compare CASCADE;
openGauss=#
openGauss=#

[lzf_cm@host2 ~]$ gaussdb --version
gaussdb (openGauss 7.0.0 build 93bfec54) compiled at 2026-08-26 10:13:12 commit 0 last mr 9389
性能结果
模式耗时相对普通 DDL
普通 ALTER TABLE 931.894 ms 100%
online ALTER TABLE 682.703 ms 73.3%

计算结果：

online 少耗时 249.191 ms
online 本次快约 26.7%
没有出现问题单中 online 接近普通 DDL 两倍耗时的现象

zhoucong<Okidoki>成员

8月26日 issue状态由待办的改变为待回归

liuzhen001成员

21 天前评论：

回归版本：7.0.0 LTS B021
回归结论：回归通过：
回归过程：

-- ==========================================================================
-- 用例：普通 ALTER TABLE 与 online ALTER TABLE 性能对比
-- 说明：每张表插入 500 万行数据，分别执行普通和在线 DDL，并记录耗时
-- ==========================================================================

-- 清理旧对象
DROP SCHEMA IF EXISTS online_ddl_perf_compare CASCADE;
CREATE SCHEMA online_ddl_perf_compare;

-- 普通 ALTER TABLE 测试表
CREATE TABLE online_ddl_perf_compare.student_offline
(
    id   bigint NOT NULL,
    name varchar(50),
    age  integer
);

-- online ALTER TABLE 测试表
CREATE TABLE online_ddl_perf_compare.student_online
(
    id   bigint NOT NULL,
    name varchar(50),
    age  integer
);

-- 创建唯一索引（确保主键唯一性）
CREATE UNIQUE INDEX student_offline_id_uq
    ON online_ddl_perf_compare.student_offline(id);
CREATE UNIQUE INDEX student_online_id_uq
    ON online_ddl_perf_compare.student_online(id);

-- 插入 500 万行数据
INSERT INTO online_ddl_perf_compare.student_offline
SELECT g, 'student_' || g, 18 + (g % 50)
  FROM generate_series(1, 5000000) g;

INSERT INTO online_ddl_perf_compare.student_online
SELECT g, 'student_' || g, 18 + (g % 50)
  FROM generate_series(1, 5000000) g;

-- 更新统计信息
ANALYZE online_ddl_perf_compare.student_offline;
ANALYZE online_ddl_perf_compare.student_online;

-- 验证行数
SELECT 'ordinary' AS test_mode, count(*) AS rows
  FROM online_ddl_perf_compare.student_offline
UNION ALL
SELECT 'online' AS test_mode, count(*) AS rows
  FROM online_ddl_perf_compare.student_online
 ORDER BY test_mode;

-- 执行检查点，确保持久化
CHECKPOINT;

-- 开启计时和设置客户端消息级别
\timing on
SET client_min_messages = notice;
SET statement_timeout = 0;

-- 普通 ALTER TABLE（加 NOT NULL 约束）
SELECT 'ordinary ALTER TABLE begin' AS test_step,
       clock_timestamp() AS event_time;

ALTER TABLE online_ddl_perf_compare.student_offline
    ALTER COLUMN age SET NOT NULL;

SELECT 'ordinary ALTER TABLE end' AS test_step,
       clock_timestamp() AS event_time;

-- online ALTER TABLE（加 NOT NULL 约束）
SELECT 'online ALTER TABLE begin' AS test_step,
       clock_timestamp() AS event_time;

ALTER TABLE CONCURRENTLY online_ddl_perf_compare.student_online
    ALTER COLUMN age SET NOT NULL;

SELECT 'online ALTER TABLE end' AS test_step,
       clock_timestamp() AS event_time;

\timing off

-- 精确行数校验（检查是否有 NULL 值）
SELECT 'ordinary' AS test_mode,
       count(*) AS actual_rows,
       count(*) FILTER (WHERE age IS NULL) AS null_rows
  FROM online_ddl_perf_compare.student_offline
UNION ALL
SELECT 'online' AS test_mode,
       count(*) AS actual_rows,
       count(*) FILTER (WHERE age IS NULL) AS null_rows
  FROM online_ddl_perf_compare.student_online
 ORDER BY test_mode;

-- 检查 age 列的 NOT NULL 属性是否已生效
SELECT c.relname AS table_name,
       a.attnotnull
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  JOIN pg_attribute a ON a.attrelid = c.oid
 WHERE n.nspname = 'online_ddl_perf_compare'
   AND c.relname IN ('student_offline', 'student_online')
   AND a.attname = 'age'
 ORDER BY c.relname;

-- 清理（测试完成后可取消注释执行）
-- DROP SCHEMA online_ddl_perf_compare CASCADE;

模式耗时相对普通 DDL
普通 ALTER TABLE Time: 488.409 ms 100%
online ALTER TABLE Time: 292.187 ms 59.82%

liuzhen001成员

21 天前评论：

liuzhen001成员

21 天前 issue状态由待回归改变为已验收

liuzhen001成员

21 天前关闭了 issue