概念描述

验证create 语句、alter 语句、truncate语句、drop语句 是属于ddl 还是dml

测试验证

1、环境准备

修改log_statement 参数
miao=# show log_statement;
log_statement
---------------
none
(1 row)

miao=# ALTER DATABASE miao SET log_statement TO ddl;
ALTER DATABASE

miao=> show log_statement;
log_statement
---------------
ddl
(1 row)

2、测试 create 的语句

创建新表t_p_t
create table t_p_t as select * from t_o_t;

create 对应的日志

2022-10-24 09:34:13.659 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [DBL_WRT] LOG: [batch flush] DW truncate end: file_head[dwn 442, start 21798], total_pages 0
2022-10-24 09:34:13.659 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [DBL_WRT] LOG: [single flush] DW truncate end: file_head[dwn 97, start 0], total_pages 0
2022-10-24 09:34:13.659 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [UNDO] LOG: [CheckPointUndoSystemMeta:353]undo metadata checkPointRedo = 20564483160.
2022-10-24 09:34:13.663 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 01000 0 [BACKEND] WARNING: replicationSlotMinLSN is InvalidXLogRecPtr!!!
2022-10-24 09:34:13.663 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 01000 0 [BACKEND] WARNING: replicationSlotMaxLSN is InvalidXLogRecPtr!!!
2022-10-24 09:34:13.664 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: CreateCheckPoint PrintCkpXctlControlFile: [checkPoint] oldCkpLoc:4/C9BC9DE0, oldRedo:4/C9BC9D60, newCkpLoc:4/C9BD1CD8, newRedo:4/C9BD1C58, preCkpLoc:4/C9BC3E58
2022-10-24 09:34:13.664 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: will update control file (create checkpoint), shutdown:0
2022-10-24 09:34:13.672 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: attempting to remove WAL segments older than log file 0000000100000004000000B7
2022-10-24 09:34:23.635 6355eb9f.3060 postgres 140420592314112 Clean Statement thread 0 dn_6001 00000 0 [BACKEND] LOG: clean statement thread start

2022-10-24 09:34:35.848 6355eb43.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: create table t_p_t as select * from t_o_t; <<<<<<<<<<<<

结论：经过以上实验验证，发现create 语句在日志里体现出来了，证明create语句属于ddl语句。

3、测试 alter table的语句

修改表t_p_t 字段大小
alter table t_p_t modify b character varying(200);


miao=> \d t_p_t
Table "dbmt.t_p_t"
Column | Type | Modifiers
--------+------------------------+-----------
a | integer |
b | character varying(255) |

miao=> alter table t_p_t modify b character varying(200);
ALTER TABLE
miao=> \d t_p_t
Table "dbmt.t_p_t"
Column | Type | Modifiers
--------+------------------------+-----------
a | integer |
b | character varying(200) |

alter table 对应的日志


2022-10-24 09:46:14.749 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: CreateCheckPoint PrintCkpXctlControlFile: [checkPoint] oldCkpLoc:4/C9C12A88, oldRedo:4/C9C12A08, newCkpLoc:4/C9C1E2B8, newRedo:4/C9C186B0, preCkpLoc:4/C9C0CDE0
2022-10-24 09:46:14.749 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: will update control file (create checkpoint), shutdown:0
2022-10-24 09:46:14.752 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [BACKEND] LOG: attempting to remove WAL segments older than log file 0000000100000004000000B7
2022-10-24 09:46:26.636 6355ee72.3060 postgres 140420185052928 Clean Statement thread 0 dn_6001 00000 0 [BACKEND] LOG: clean statement thread start


2022-10-24 09:47:07.321 6355eb43.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: alter table t_p_t modify b character varying(200); <<<<<<<<<<<<

结论：经过以上实验验证，发现alter 语句在日志里体现出来了，证明alter 语句属于ddl语句。
4、测试 truncate 的语句

truncate table t_p_t；


truncate 没有在ddl状态下输出日志

5、测试 drop 的语句

drop table t_p_t;

drop 对应的日志

2022-10-24 09:52:28.317 6355efdc.3060 postgres 140420185052928 Clean Statement thread 0 dn_6001 00000 0 [BACKEND] LOG: clean statement thread start
2022-10-24 09:52:33.182 6355eb43.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: drop table t_p_t; <<<<<<<<<

结论：经过以上实验验证，发现drop 语句在日志里体现出来了，证明drop 语句属于ddl语句。

6、修改log_statement 参数为mod 再次测试truncate语句

miao=> ALTER DATABASE miao SET log_statement TO mod;
ALTER DATABASE
miao=> \q
[omm@db1 gs_dump]$ gsql -d miao -p 26000 -U dbmt
Password for user dbmt:
gsql ((MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

miao=> show log_statement;
log_statement
---------------
mod
(1 row)

miao=> truncate table t_p_t;
TRUNCATE TABLE

truncate 对应的日志
2022-10-24 09:58:08.817 6355f0ee.3061 miao 140421053867776 gsql 0 dn_6001 00000 0 [BACKEND] LOG: statement: truncate table t_p_t; <<<<<<<<<<
2022-10-24 09:58:08.817 6355f0ee.3061 miao 140421053867776 gsql 1588850 dn_6001 00000 17732923532782651 [BACKEND] LOG: Relation t_p_t(165435) set newfilenode 165441 oldfilenode 165438 xid 1588850
2022-10-24 09:58:14.810 635515e3.3858 [unknown] 140420986697472 dn_6001 0 dn_6001 00000 0 [DBL_WRT] LOG: [batch flush] DW truncate end: file_head[dwn 442, start 26764], total_pages 0

结论：经过以上实验验证，发现truncate 语句在日志里体现出来了，证明truncate 语句属于dML语句。

知识总结

drop 语句、create 语句、drop 语句、alter 语句 都属于ddl
truncate 属于dml语句