postgresql 修改表字段_分析DDL语句对PostgreSQL做了什么

最新推荐文章于 2023-12-22 22:18:36 发布
最新推荐文章于 2023-12-22 22:18:36 发布
阅读量277 收藏
点赞数
文章标签： postgresql 修改表字段
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_32206303/article/details/112328737
版权
本文介绍了一种通过SQL方式分析DDL语句对PostgreSQL系统表影响的方法。借助walminer工具，不仅可以查看普通表的变更，还能解析系统表的改动。通过示例展示了如何监控和理解DDL导致的系统表DML操作，为进一步完善对DDL语句的解析奠定了基础。
摘要由CSDN通过智能技术生成

ff59c3ba39ab16e7c6f75e2a1adbc1c0.png

Pger都知道，当我们对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项目不迷路，及时了解项目开发动态。

寒霜血蝶
关注
【PostgreSQL】PostgreSQL函数-得到表的建表语句、得到整个schema的ddl语句
tttzzzqqq2018的博客
01-19 2861
【代码】【PostgreSQL】PostgreSQL函数-得到表的建表语句、得到整个schema的ddl语句
PostgreSQL DDL语句、DML语句与DCL语句
椟夜
08-11 4899
SQL 语句主要可以划分为以下 3 个类别 DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。 DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，...
Postgresql - 执行DDL - 锁问题的避免
chuckchen1222的博客
11-18 889
加列，减列很快，几乎没影响 Version 10 alter table test01 add column col11 varchar(100) alter table test01 alter COLUMN col12 set default 'abc'; Version 11 alter table test01 add column col11 varchar(100) default...
Ruby Postgresql 并行加索引避免锁表
WoBenZiYou的专栏
09-29 234
在 Rails 4 之後可以直接加入 algorithm: :concurrently 在你的 migration 檔案內 例如： class AddIndexToUsers < ActiveRecord::Migration disable_ddl_transaction! def change add_index :users, :alive, algo...
PostgreSQL sharding : citus 系列4 - DDL 操作规范 (新增DB，TABLE，SCHEMA，UDF，O...
weixin_34259232的博客
04-14 355
标签 PostgreSQL , citus , 新增对象 , 新增数据库 , 新增用户 背景 citus是PG的一个插件，插件主要针对普通SQL（非UTILITY）加HOOK进行了一些ROUTE处理，同时使用UDF对表进行新建分区的操作。 如果用户如果要执行DDL，那么CITUS无法接管，应该如何操作呢？ 分两种情况，一种需要在所有节点（CN以及WO...
PostgreSQL中的DDL支持回滚
PostgreSQL China
08-26 1282
作者：小麦苗 在Oracle或MySQL中，当执行到DDL语句时，会隐式的将当前回话的事务进行一次“COMMIT”操作，因此在MySQL或Oracle中执行DDL语句时，应该严格地将DDL和DML完全分开，不能混合在一起执行。 在PG中，DDL语句是可以被回滚的。 Oracle SYS@LHR11G> SET TRANSACTION NAME 't1'; Transaction set. SYS@LHR11G> create table aa(id int); Table created. SY
pg_get_table_ddl:PostgreSQL PLPGSQL函数，可为给定的schematable生成表DDL
03-21
PostgreSQL PL / PGSQL函数，它为给定的模式/表生成表DDL。 （c）2021 SQLEXEC LLC GNU V3和MIT许可证也相应地传达。可以在上报告错误 历史 生成表DDL有多种形式，这是要添加到列表中的另一种形式。我认为这比我在...
ddlparse：DDL parase并转换为BigQuery JSON模式和DDL语句
01-28
DDL parase并转换为BigQuery JSON模式和DDL语句模块，可在Python中使用。 特征 DDL解析并获取表架构信息。 当前，仅支持CREATE TABLE语句。 转换为和。 支持的数据库是MySQL / MariaDB，PostgreSQL，Oracle，...
pgquarrel：pgquarrel比较PostgreSQL数据库模式（DDL）
02-05
在PostgreSQL中，DDL是SQL的一个子集，用于创建和修改数据库对象的语句，例如`CREATE TABLE`，`ALTER TABLE`，`DROP TABLE`等。pgquarrel通过解析和比较两个数据库的DDL，提供清晰的差异报告，便于理解和解决差异。 ...
【PG恢复被打了删除标记的列】
小怪兽的博客
01-19 1479
环境 PostgreSQL 12.1 一、创建测试数据 postgres=# create table ysl1(id int,name char(10)); CREATE TABLE postgres=# insert into ysl1 values(1,'qwe'); INSERT 0 1 postgres=# insert into ysl1 values(2,'q32'); INSERT 0 1 postgres=# \dt+ ysl1 List
【PostgreSQL表增加/删除字段是否会重写表】
最新发布
小怪兽的博客
12-22 2445
PostgreSQL 10 版本前表新增不带默认值的列不需要重写表，只需要更新数据字典，因此能瞬间执行。如果不带默认值，则会填充空值。如果新增的字段带默认值，则需要重写表。表越大，执行时间越长。重写表会对表加Access Exclusive锁，期间整张表是无法访问的。如果是生产环境下给大表添加带 Default 值的字段可能影响比较大，通常先添加不带 Default值的字段，然后写函数批量刷新新增字段的默认值。也可以在业务量较低的时候或者申请割接窗口停业务一次性完成带DEFAUL值字段的新增。
postgres store the dropped columns in pg_attribute
惊鸿一瞥
12-28 649
in postgres when we dropped colunms, in pg_attribute we still could see such as … pg.dropped.4…, therefore when you use pg_atrribute would like to select all the colunms of the table, you need to cons...
pg-数据库审计
weixin_33958366的博客
09-05 389
2019独角兽企业重金招聘Python工程师标准>>> ...
技术实践丨PostgreSQL插件之pg_dirtyread “闪回查询“
华为云官方博客
10-30 2619
摘要：Oracle数据库有时候不小心删除掉数据，想查询这些数据，或者恢复数据，就可以使用带有as of子句的select语句进行闪回查询。 PG粉有福了，下面介绍一种类似“闪回查询”插件 pg_dirtyread，可以读取未被vacuum的dead数据。 github主页：https://github.com/df7cb/pg_dirtyread 1.2 released：https://www.postgresql.org/message-id/20170923211004.uh27ncpjarku
Postgresql系统表pg_attrdef
HighGO
02-27 1366
该系统表主要存储字段缺省值，字段中的主要信息存放在pg_attribute系统表中。注意：只有明确声明了缺省值的字段在该表中才会有记录。名字类型引用描述adrelidoidpg_class.oid这个字段所属的表adnumint2pg_attribute.attnum字段编号，其规则等同于pg_attribute.attnumadbintext字段缺省值的内部表现形式。adsrctex...
DML和DCL、DDL的Sql Server修改字段属性和结构以及约束
忍冬草的专栏
08-24 584
1：向表中添加字段 Alter table [表名] add [列名] 类型 2: 删除字段 Alter table [表名] drop column [列名] 3: 修改表中字段类型 （可以修改列的类型，是否为空） Alter table [表名] alter column [列名] 类型 4：添加主键 Alter table [表名] add constraint [ 约束名] 
PostgreSQL 事件触发器应用 - DDL审计
weixin_33824363的博客
10-28 834
标签 PostgreSQL , 事件触发器 , DDL审计 , 表结构变更 , 建表等审计 背景 DDL语句的审计是非常重要的，目前PG的DDL审计记录在日志文件中。不便于查看。 为了让DDL事件记录到表中，方便查看，我们可以通过事件触发器来达到这个效果。 事件触发器审计DDL操作 事件触发器语法: Command: CREATE EVEN...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值