PostgreSQL 将字段加入指定位置 - 表字段位置的 虚拟修改 实现

背景

在某些场景中，用户可能希望在原有字段的某个位置增加一个字段，例如

alter table test add column c1 int after id;  

在id字段后面添加一个字段。

在PostgreSQL中，可以通过sql rewrite来做到同样的功能。

实际上是用到了PostgreSQL的simple view。 simple view会自动添加view的insert\update\delete等rule，读写view就和读写TABLE一样。

先了解PostgreSQL的物理存储，在PG中，数据是tuple组织的，每个tuple都是固定的storage layout，即字段存储的物理顺序是固定的，解释时是按照pg_attribute中存储的顺序。

postgres=# select attname,attnum,attisdropped from pg_attribute where attrelid ='tbl'::regclass;  
 attname  | attnum | attisdropped   
----------+--------+--------------  
 tableoid |     -7 | f  
 cmax     |     -6 | f  
 xmax     |     -5 | f  
 cmin     |     -4 | f  
 xmin     |     -3 | f  
 ctid     |     -1 | f  
 id       |      1 | f  
 info     |      2 | f  
 crt_time |      3 | f  
 c1       |      4 | f  
(10 rows)  

那么怎么能做到用户看到的顺序是可以变的呢？

例子

使用简单视图，即rewrite rule.

postgres=# create table tbl(id int, info text, crt_time timestamp);  
CREATE TABLE  
Time: 15.285 ms  
postgres=# alter table tbl add column c1 int;  
ALTER TABLE  
Time: 12.872 ms  
postgres=# create view v_tbl as select id,info,c1,crt_time from tbl;  
CREATE VIEW  
Time: 0.889 ms  
postgres=# insert into v_tbl values (1,'test',2,now());  
INSERT 0 1  
Time: 1.208 ms  
postgres=# select * from v_tbl  
postgres-# ;  
 id | info | c1 |          crt_time            
----+------+----+----------------------------  
  1 | test |  2 | 2016-02-29 14:07:19.171928  
(1 row)  
  
Time: 0.544 ms  
postgres=# select * from tbl;  
 id | info |          crt_time          | c1   
----+------+----------------------------+----  
  1 | test | 2016-02-29 14:07:19.171928 |  2  
(1 row)  
  
Time: 0.282 ms  
postgres=# select attname,attnum,attisdropped from pg_attribute where attrelid ='tbl'::regclass;  
 attname  | attnum | attisdropped   
----------+--------+--------------  
 tableoid |     -7 | f  
 cmax     |     -6 | f  
 xmax     |     -5 | f  
 cmin     |     -4 | f  
 xmin     |     -3 | f  
 ctid     |     -1 | f  
 id       |      1 | f  
 info     |      2 | f  
 crt_time |      3 | f  
 c1       |      4 | f  
(10 rows)  
  
Time: 0.708 ms  

使用VIEW后，对基表执行DDL，会传染给VIEW，必须要干掉VIEW重建。

postgres=# alter table tbl drop column info;  
ERROR:  cannot drop table tbl column info because other objects depend on it  
DETAIL:  view v_tbl depends on table tbl column info  
HINT:  Use DROP ... CASCADE to drop the dependent objects too.  
Time: 8.794 ms  
  
postgres=# alter table tbl drop column info cascade;   
NOTICE:  drop cascades to view v_tbl  
ALTER TABLE  
Time: 1.561 ms  
postgres=# \d v_t  
  
postgres=# create view v_tbl as select id,c1,crt_time from tbl;  
CREATE VIEW  
Time: 2.248 ms  
postgres=# select attname,attnum,attisdropped from pg_attribute where attrelid ='tbl'::regclass;  
           attname            | attnum | attisdropped   
------------------------------+--------+--------------  
 tableoid                     |     -7 | f  
 cmax                         |     -6 | f  
 xmax                         |     -5 | f  
 cmin                         |     -4 | f  
 xmin                         |     -3 | f  
 ctid                         |     -1 | f  
 id                           |      1 | f  
 ........pg.dropped.2........ |      2 | t  
 crt_time                     |      3 | f  
 c1                           |      4 | f  
(10 rows)  
  
Time: 0.675 ms  
postgres=# insert into v_tbl values (1,2,now());  
INSERT 0 1  
Time: 0.370 ms  
postgres=# select * from v_tbl;  
 id | c1 |          crt_time            
----+----+----------------------------  
  1 |  2 | 2016-02-29 14:07:19.171928  
  1 |  2 | 2016-02-29 14:09:18.499834  
(2 rows)  
  
Time: 0.295 ms  
postgres=# select * from tbl;  
 id |          crt_time          | c1   
----+----------------------------+----  
  1 | 2016-02-29 14:07:19.171928 |  2  
  1 | 2016-02-29 14:09:18.499834 |  2  
(2 rows)  
  
Time: 0.375 ms  

所以万不得已，也不要这么用。除非业务上不想改SQL。

参考

https://yq.aliyun.com/articles/7176

simple view

digoal’s 大量PostgreSQL文章入口