一、前言
在需求的不斷疊代中,表字段也會增加,有時候在大表增加的字段中,存在包含not null和default的字段,這時候添加表字段就執行相當慢,因為PostgreSQL把表資料全部重寫,參考:https://my.oschina.net/Kenyon/blog/99757
現提供一種修改系統表的方式來快速在大表中增加表字段,避免資料重寫,參考:https://my.oschina.net/Suregogo/blog/1605598
二、3個相關的系統表
1. pg_class: 記錄所有relation的記錄;
-
relnatts: 表中使用者列的數目(系統列不計算在内)
其他參考: http://www.postgres.cn/docs/9.5/catalog-pg-class.html
2. pg_attribute: 記錄表字段的記錄
- attrelid: 所屬表的oid;
- attname: 字段名
- attnum: 列的編号;
- attnotnull:是否非空限制;
-
atthasdef: 是否有預設值;
其他參考: http://www.postgres.cn/docs/9.5/catalog-pg-attribute.html
3. pg_attrdef: 記錄字段的預設值資訊
- oid: 字段的oid;
- adrelid: 所屬表的oid;
-
adsrc: 預設值的可視化表示;
其他參考: http://www.postgres.cn/docs/9.5/catalog-pg-attrdef.html
三、測試
1. 建立測試表
CREATE TABLE public.address
(
sid varchar primary key,
country character varying,
province character varying,
detail character varying,
state integer NOT NULL DEFAULT 0
)
2. 假如需求增加一個字段a,預設值是0,如下:
alter table address add column a int default 0;
如果表很大,這句執行語句很慢;
3. 替代方案
3.1 思路
在系統表中參考已經存在的相同類型的字段state,在pg_attribute表中增加一條記錄,在pg_class的字段數加1,如有預設值,在pg_attrdef增加一條記錄;
3.2 檢視目前系統表
pg_class 表
postgres=# select * from pg_class where relname='address';
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relfrozenxid | relminmxid | relacl | reloptions
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+--------------+------------+--------+------------
address | 2200 | 669725 | 0 | 10 | 0 | 669723 | 0 | 0 | 0 | 0 | 669727 | t | f | p | r | 5 | 0 | f | t | f | f | f | f | f | t | d | 58977486 | 1 | |
(1 row)
pg_attribute 表
postgres=# select * from pg_attribute where attrelid ='address'::regclass order by attnum desc;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions
----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------+--------------+--------+------------+---------------
669723 | state | 23 | -1 | 4 | 5 | 0 | -1 | -1 | t | p | i | t | t | f | t | 0 | 0 | | |
669723 | detail | 1043 | -1 | -1 | 4 | 0 | -1 | -1 | f | x | i | f | f | f | t | 0 | 100 | | |
669723 | province | 1043 | -1 | -1 | 3 | 0 | -1 | -1 | f | x | i | f | f | f | t | 0 | 100 | | |
669723 | country | 1043 | -1 | -1 | 2 | 0 | -1 | -1 | f | x | i | f | f | f | t | 0 | 100 | | |
669723 | sid | 1043 | -1 | -1 | 1 | 0 | -1 | -1 | f | x | i | t | f | f | t | 0 | 100 | | |
669723 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | f | t | 0 | 0 | | |
669723 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | 0 | | |
669723 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | 0 | | |
669723 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | 0 | | |
669723 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | 0 | | |
669723 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0 | 0 | | |
(11 rows)
pg_attrdef 表
postgres=# select * from pg_attrdef where adrelid ='address'::regclass;
adrelid | adnum | adbin | adsrc
---------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+-------
669723 | 5 | {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 183 :constvalue 4 [ 0 0 0 0 0 0 0 0 ]} | 0
(1 row)
postgres=#
這裡pg_attrdef.adnum=pg_attribute.attnum=5,指的就是state字段,adsrc就是預設值0;
3.3. 增加字段
- 參考state在pg_attribute中加一個記錄,表示字段a,這裡attnotnull=f 表示不是not null,atthasdef=t 表示有預設值;
insert into pg_attribute (
attrelid,attname,atttypid,attstattarget,attlen,attnum,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,attnotnull,atthasdef,attisdropped,attislocal,attinhcount,attcollation,attacl,attoptions,attfdwoptions
)
select
attrelid,'a',atttypid,attstattarget,attlen,attnum+1,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,'f','t',attisdropped,attislocal,attinhcount,attcollation,attacl,attoptions,attfdwoptions
from pg_attribute where attrelid='address'::regclass and attname='state';
- pg_class字段數量加1
update pg_class set relnatts=relnatts+1 where relname='address';
- pg_attrdef 增加一條記錄,表示添加預設值
with t as(select max(attnum) as maxAttNum from pg_attribute where attrelid='address'::regclass)
insert into pg_attrdef(adrelid,adnum,adbin,adsrc) select adrelid,maxAttNum,adbin,adsrc from pg_attrdef,t where adrelid='address'::regclass and adnum=(select attnum from pg_attribute where attrelid ='address'::regclass and attname='state');
3.4 檢視表結構
postgres=# \d+ address
Table "public.address"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-------------------+--------------------+----------+--------------+-------------
sid | character varying | not null | extended | |
country | character varying | | extended | |
province | character varying | | extended | |
detail | character varying | | extended | |
state | integer | not null default 0 | plain | |
a | integer | default 0 | plain | |
Indexes:
"address_pkey" PRIMARY KEY, btree (sid)
可以看到字段a已經加上;
四、編寫Function添加字段
4.1 函數
create or replace function func_fast_add_column(tableName varchar,referenceColumn varchar,newColumnName varchar,isNotNull boolean,hasDefaultValue boolean) returns void as $$
--快速向大表中加字段
--tableName:表名
--referenceColumn:新字段的類型,預設值等參考的字段
--newColumnName:新字段名稱
--isNotNull:是否非空
--hasDefaultValue:是否有預設值
declare
currentAttNum int; --目前表的字段最大序号
begin
select max(attnum) into currentAttNum from pg_attribute where attrelid=tableName::regclass;
--1. 添加字段屬性 --attnotnull 表示是否非空
insert into pg_attribute (attrelid,attname,atttypid,attstattarget,attlen,attnum,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,attnotnull,atthasdef,attisdropped,attislocal,attinhcount,attcollation,attacl,attoptions,attfdwoptions )
select attrelid,newColumnName,atttypid,attstattarget,attlen,currentAttNum+1,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,isNotNull,hasDefaultValue,attisdropped,attislocal,attinhcount,attcollation,attacl,attoptions,attfdwoptions from pg_attribute where attrelid=tableName::regclass and attname=referenceColumn;
--2. 修改pg_class字段個數
update pg_class set relnatts=relnatts+1 where relname=tableName;
--3. 添加預設值 adnum:列号
if(hasDefaultValue) then
insert into pg_attrdef(adrelid,adnum,adbin,adsrc) select adrelid,currentAttNum+1,adbin,adsrc from pg_attrdef where adrelid=tableName::regclass and adnum=(select attnum from pg_attribute where attrelid =tableName::regclass and attname=referenceColumn);
end if;
end
$$language plpgsql;
4.2 添加字段b
alter table address column b int not null default 0;
或者
select func_fast_add_column('address','state','b',true,true);
4.3 檢視表結構
postgres=# \d+ address
Table "public.address"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-------------------+--------------------+----------+--------------+-------------
sid | character varying | not null | extended | |
country | character varying | | extended | |
province | character varying | | extended | |
detail | character varying | | extended | |
state | integer | not null default 0 | plain | |
a | integer | default 0 | plain | |
b | integer | not null default 0 | plain | |
Indexes:
"address_pkey" PRIMARY KEY, btree (sid)
五、風險
與正常添加字段後有啥差別,有啥風險尚不明确;