原作者：刘继超

• 概念描述
• 测试验证
• 知识总结

概念描述

验证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语句