分析DDL语句对PostgreSQL做了什么

开源软件联盟PostgreSQL分会 2020-08-21

1460

本文作者：李传成

中国PG分会认证专家，瀚高软件资深内核研发工程师

分析DDL语句对PostgreSQL做了什么

资深的PostgreSQL DBA都知道，当我们对PostgreSQL执行一条DDL语句时，PostgreSQL会在pg_class,pg_attribute等系统表执行DML操作，比如执行了CREATE TABLE t1(i int, j int);DDL语句，那么就会在pg_class里插入一条't1'的记录，会在pg_attribute里插入‘i’,'j'还有其他隐藏字段的记录。但是PostgreSQL所做的不仅仅是我们认知的这一点点东西，比如表带有索引，约束，text字段等会往一些表里插入不同的数据。也许你有一些甄别DDL引起的系统表的变化的方法，比如对系统表进行比对，今天给大家带来一种更直观的方法，以SQL的方式展现一条DDL语句对系统表所做的改动。废不多说，直接上货.....

分析方法

有熟悉walminer这个工具的同学，可能了解，walminer可以从wal日志中解析出SQL语句。悄悄的告诉大家PostgreSQL内核中对系统表和对普通表的修改都是一样的接口，都会在wal中产生同样的记录，把walminer的代码轻轻一改，就能解析系统表的变更了，最近我开放了系统表解析的接口，迫不及待跟大家分享一下...

方法演示

正常walminer解析普通表是同样的步骤，只不过在解析之前需要执行select walminer_with_catalog();通知walminer你需要系统表的更改。

-- 数据准备
select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/3D07AC28
(1 row)
 
postgres=# create table t200( i int, j int);
CREATE TABLE
 
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/3D08C0E8
(1 row)

-- 执行解析
postgres=# select walminer_with_catalog();
 walminer_with_catalog
-----------------------
 t
(1 row)
 
postgres=# select wal2sql('0/3D07AC28'::pg_lsn, '0/3D08C0E8');
NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE:  Switch wal to 00000001000000000000003D on time 2020-08-05 22:04:06.506044+08
       wal2sql       
---------------------
 pg_minerwal success
(1 row)

-- 见证奇迹
-- 系统表字段太多，导致结果不是很美观，但不妨碍其正确性
postgres=# select op_text from walminer_contents;


op_text

                                                                                                                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
 INSERT INTO pg_catalog.pg_type(oid ,typname ,typnamespace ,typowner ,typlen ,typbyval ,typtype ,typcategory ,typispreferred ,typisdefined ,typdelim ,typrelid ,typelem ,typarra
y ,typinput ,typoutput ,typreceive ,typsend ,typmodin ,typmodout ,typanalyze ,typalign ,typstorage ,typnotnull ,typbasetype ,typtypmod ,typndims ,typcollation ,typdefaultbin ,t
ypdefault ,typacl) VALUES(76134 ,'t200' ,2200 ,10 ,-1 ,false ,'c' ,'C' ,false ,true ,',' ,76132 ,0 ,76133 ,'record_in' ,'record_out' ,'record_recv' ,'record_send' ,'-' ,'-' ,'-
' ,'d' ,'x' ,false ,0 ,-1 ,0 ,0 ,null ,null ,null)
 INSERT INTO pg_catalog.pg_depend(classid ,objid ,objsubid ,refclassid ,refobjid ,refobjsubid ,deptype) VALUES(1247 ,76134 ,0 ,1259 ,76132 ,0 ,'i')
 INSERT INTO pg_catalog.pg_type(oid ,typname ,typnamespace ,typowner ,typlen ,typbyval ,typtype ,typcategory ,typispreferred ,typisdefined ,typdelim ,typrelid ,typelem ,typarra
y ,typinput ,typoutput ,typreceive ,typsend ,typmodin ,typmodout ,typanalyze ,typalign ,typstorage ,typnotnull ,typbasetype ,typtypmod ,typndims ,typcollation ,typdefaultbin ,t
ypdefault ,typacl) VALUES(76133 ,'_t200' ,2200 ,10 ,-1 ,false ,'b' ,'A' ,false ,true ,',' ,0 ,76134 ,0 ,'array_in' ,'array_out' ,'array_recv' ,'array_send' ,'-' ,'-' ,'array_ty
panalyze' ,'d' ,'x' ,false ,0 ,-1 ,0 ,0 ,null ,null ,null)
 INSERT INTO pg_catalog.pg_depend(classid ,objid ,objsubid ,refclassid ,refobjid ,refobjsubid ,deptype) VALUES(1247 ,76133 ,0 ,1247 ,76134 ,0 ,'i')
 INSERT INTO pg_catalog.pg_class(oid ,relname ,relnamespace ,reltype ,reloftype ,relowner ,relam ,relfilenode ,reltablespace ,relpages ,reltuples ,relallvisible ,reltoastrelid
,relhasindex ,relisshared ,relpersistence ,relkind ,relnatts ,relchecks ,relhasrules ,relhastriggers ,relhassubclass ,relrowsecurity ,relforcerowsecurity ,relispopulated ,relre
plident ,relispartition ,relrewrite ,relfrozenxid ,relminmxid ,relacl ,reloptions ,relpartbound) VALUES(76132 ,'t200' ,2200 ,76134 ,0 ,10 ,2 ,76132 ,0 ,0 ,0 ,0 ,0 ,false ,false
 ,'p' ,'r' ,2 ,0 ,false ,false ,false ,false ,false ,true ,'d' ,false ,0 ,'2987' ,'1' ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'i' ,23
 ,-1 ,4 ,1 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,false ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'j' ,23
 ,-1 ,4 ,2 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,false ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'ctid'
,27 ,0 ,6 ,-1 ,0 ,-1 ,-1 ,false ,'p' ,'s' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'xmin'
,28 ,0 ,4 ,-2 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'cmin'
,29 ,0 ,4 ,-3 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'xmax'
,28 ,0 ,4 ,-4 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'cmax'
,29 ,0 ,4 ,-5 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_attribute(attrelid ,attname ,atttypid ,attstattarget ,attlen ,attnum ,attndims ,attcacheoff ,atttypmod ,attbyval ,attstorage ,attalign ,attnotnull ,a
tthasdef ,atthasmissing ,attidentity ,attgenerated ,attisdropped ,attislocal ,attinhcount ,attcollation ,attacl ,attoptions ,attfdwoptions ,attmissingval) VALUES(76132 ,'tableo
id' ,26 ,0 ,4 ,-6 ,0 ,-1 ,-1 ,true ,'p' ,'i' ,true ,false ,false ,'' ,'' ,false ,true ,0 ,0 ,null ,null ,null ,null)
 INSERT INTO pg_catalog.pg_depend(classid ,objid ,objsubid ,refclassid ,refobjid ,refobjsubid ,deptype) VALUES(1259 ,76132 ,0 ,2615 ,2200 ,0 ,'n')
(14 rows)

一个例子不过瘾，再来看一下比较清晰明了的例子

-- 数据准备
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/3D08CA38
(1 row)
postgres=# alter table t200 drop column j;
ALTER TABLE
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/3D08F010
(1 row)

-- 执行解析
postgres=# select walminer_with_catalog();
 walminer_with_catalog
-----------------------
 t
(1 row)
postgres=# select wal2sql('0/3D08CA38'::pg_lsn,'0/3D08F010'::pg_lsn);
NOTICE:  Add wal from current pg_wal directory, do not suggest use this way in produce
NOTICE:  Switch wal to 00000001000000000000003D on time 2020-08-05 22:28:31.345227+08
       wal2sql       
---------------------
 pg_minerwal success
(1 row)

-- 见证奇迹
postgres=# select op_text from walminer_contents;
                                                                                                                                                                           


op_text
 

-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
UPDATE pg_catalog.pg_attribute SET attrelid=76132, attname='........pg.dropped.2........', 
atttypid=0, attstattarget=0, attlen=4, attnum=2, attndims=0, attcacheoff=-1, atttypmod=-1, 
attbyval=true, attstorage='p', attalign='i', attnotnull=false, atthasdef=false, atthasmissing=false, 
attidentity='', attgenerated='', attisdropped=true, attislocal=true, attinhcount=0, attcollation=0, 
attacl=null, attoptions=null, attfdwoptions=null, attmissingval=null
WHERE attrelid=76132 AND attname='j' AND atttypid=23 AND attstattarget=-1 AND attlen=4 
AND attnum=2 AND attndims=0 AND attcacheoff=-1 AND atttypmod=-1 AND attbyval=true 
AND attstorage='p' AND attalign='i' AND attnotnull=false AND atthasdef=false AND 
atthasmissing=false AND attidentity='' AND attgenerated='' AND attisdropped=false AND 
attislocal=true AND attinhcount=0 AND attcollation=0 AND attacl=null AND attoptions=null 
AND attfdwoptions=null AND attmissingval=null
(1 row)
postgres=#

引申

每一个DDL都对应者一组对系统表的DML的集合，理论上walminer可以完成直接输出DDL语句的工作。但是DDL语句真是太多了，这会是一个庞大的工作量。我准备从最基础的DDL语句入手，逐步完善walminer工具对DDL的解析，大家可以关注walminer(https://gitee.com/movead/XLogMiner)项目不迷路，及时了解项目开发动态。


I Love PG

关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。

欢迎投稿

做你的舞台，show出自己的才华。

投稿邮箱：partner@postgresqlchina.com

——愿能安放你不羁的灵魂

技术文章精彩回顾




PostgreSQL学习的九层宝塔
PostgreSQL职业发展与学习攻略
2019，年度数据库舍 PostgreSQL 其谁?
Postgres是最好的开源软件
PostgreSQL是世界上最好的数据库
从Oracle迁移到PostgreSQL的十大理由
从“非主流”到“潮流”，开源早已值得拥有

PG活动精彩回顾




创建PG全球生态！PostgresConf.CN2019大会盛大召开
首站起航！2019“让PG‘象’前行”上海站成功举行
走进蓉城丨2019“让PG‘象’前行”成都站成功举行
中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行
群英论道聚北京，共话PostgreSQL
相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报
相知巴厘岛| PG Conf.Asia 2019 DAY2简报
独家|硅谷Postgres大会简报
直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布

PG培训认证精彩回顾




中国首批PGCA认证考试圆满结束，203位考生成功获得认证！
中国第二批PGCA认证考试圆满结束，115位考生喜获认证！
重要通知：三方共建，中国PostgreSQL认证权威升级！
近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！
2020年首批 | 中国PostgreSQL初级认证考试圆满结束
一分耕耘一分收获，第五批次PostgreSQL认证考试成绩公布

postgresql

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

分析DDL语句对PostgreSQL做了什么

分析DDL语句对PostgreSQL做了什么

分析方法

方法演示

引申

评论