作者
digoal
日期
2019-08-04
标签
PostgreSQL , 隐藏列 , oids , inherit
背景
无主键表，在更新，删除时，目标端端回放可能会有问题：
1、更新或删除源、目标不一致。
update a set id=2 where ctid=(0,1);
ctid=(0,1)，指物理行号，逻辑解析时没有物理行号信息，并且上下游的物理行号也不可能一一对应起来。
解析出来的信息可能是这样的：
old tuple: id=?,c=?c2=?...
new tuple: id=?,c=?c2=?...
回放时的sql封装成这样
update a set id=?,c=?c2=? where id=?,c=?c2=?;
然而源端只更新了一条，而目标端，可能更新多条（当id=?,c=?c2=?有重复记录时）。
2、回放需要扫描全表，导致慢
因为没有主键或uk，目标端回放时，where使用full columns过滤，需要扫描全表，导致性能差。
思路
1、源端有唯一隐藏列时，目标端增加隐藏列。
2、源端没有唯一隐藏列时，源端、目标端都需要增加这个隐藏列。
回放时，需要拿到隐藏列的old,new value，同时需要有源端，目标端隐藏列名的映射关系。封装出对应的SQL。
例子
1、Oracle logical rowid。
2、Oracle 12c 支持隐藏列的语法，查询和插入时，隐藏列必须明确指定，否则就好像不存在这个列：
```
anbob@PDB1>create table test(id int,id1 int,id2 int,id3 int);
Table created.
anbob@PDB1>insert into test values(1,2,3,4);
1 row created.
anbob@PDB1>commit;
Commit complete.
anbob@PDB1>desc test;
Name Null? Type
ID NUMBER(38)
ID1 NUMBER(38)
ID2 NUMBER(38)
ID3 NUMBER(38)
anbob@PDB1>select * from test;
ID ID1 ID2 ID3
1 2 3 4复制
anbob@PDB1>alter table test modify id2 invisible;
Table altered.
anbob@PDB1>select * from test;
ID ID1 ID3
1 2 4 anbob@PDB1>desc test;复制
Name Null? Type
ID NUMBER(38)
ID1 NUMBER(38)
ID3 NUMBER(38)
anbob@PDB1>alter table test modify id1 invisible;
Table altered.
anbob@PDB1>desc test;
Name Null? Type
ID NUMBER(38)
ID3 NUMBER(38)
anbob@PDB1>insert into test values(11,12,13,14);
insert into test values(11,12,13,14)
*
ERROR at line 1:
ORA-00913: too many values
anbob@PDB1>insert into test(id,id1,id2,id3) values(11,12,13,14);
1 row created.
anbob@PDB1>commit;
Commit complete.
anbob@PDB1>select * from test;
ID ID3
1 4 11 14复制
anbob@PDB1>select id,id1,id2,id3 from test;
ID ID1 ID2 ID3
1 2 3 4 11 12 13 14复制
```
3、pg oid，oid为隐藏列。但是最大只能存储40亿条。也就是单表的记录数不能超过40亿，否则可能重复。
```
postgres=# create table st5 (id int, info text, crt_time timestamp) with (oids);
CREATE TABLE
postgres=# alter table st5 add constraint pk_st5 primary key(oid);
ALTER TABLE
postgres=#
postgres=# insert into st5 values (1,'a',now());
INSERT 26635 1
postgres=#
postgres=# select * from st5;
id | info | crt_time
----+------+----------------------------
1 | a | 2019-08-04 12:30:17.004302
(1 row)
postgres=#
postgres=# select oid from st5;
oid
26635
(1 row)
```
4、pg inherits，继承表增加隐藏字段，真实的数据存储在继承表，主表是用户使用的表（没有隐藏字段）。使用触发器，将数据写入到继承表。
需要注意的是，复制时，解析出来的是继承表的变化，而不是主表。
```
postgres=# create table orig (id int, info text, crt_time timestamp);
CREATE TABLE
创建hid继承表，增加的主键可以使用serial8或者使用identify语法。
postgres=# create table hid_orig (id int, info text, crt_time timestamp, hid serial8 primary key) inherits(orig); NOTICE: merging column "id" with inherited definition NOTICE: merging column "info" with inherited definition NOTICE: merging column "crt_time" with inherited definition CREATE TABLE
postgres=# create or replace function tg() returns trigger as $$
declare
begin
insert into hid_orig (id,info,crt_Time) values (new.*);
return null;
end;
$$ language plpgsql strict;
CREATE FUNCTION
postgres=# create trigger tg before insert on orig for each row execute procedure tg();
CREATE TRIGGER
postgres=# insert into orig values (1,'test',now());
INSERT 0 0
postgres=# select * from orig;
id | info | crt_time
----+------+----------------------------
1 | test | 2019-08-04 12:33:55.909658
(1 row)
postgres=# select * from hid_orig;
id | info | crt_time | hid
----+------+----------------------------+-----
1 | test | 2019-08-04 12:33:55.909658 | 1
(1 row)
postgres=# update orig set info='new' where id=1;
UPDATE 1
postgres=# select * from orig;
id | info | crt_time
----+------+----------------------------
1 | new | 2019-08-04 12:33:55.909658
(1 row)
postgres=# select * from hid_orig;
id | info | crt_time | hid
----+------+----------------------------+-----
1 | new | 2019-08-04 12:33:55.909658 | 1
(1 row)
```
5、pg 类似oracle的隐藏列，暂时还没有支持，将来可能会支持。
建议
建议所有需要逻辑复制的表，都有主键。（除非你的表只有insert，没有update和delete）
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
