作者
digoal
日期
2017-11-08
标签
PostgreSQL , Oracle , 兼容性 , 行号 , rowid , oid , ctid
背景
Oracle的数据中，通过ROWID可以定位到一条记录，当记录没有发生行迁移时，ROWID是不变的，因此即使不使用PK，也能很好的定位到一条记录。
PostgreSQL中，也有行号，CTID，由BLOCK_ID和ITEM_ID组成，即哪个数据块的哪条记录。
但是PostgreSQL的引擎为多版本引擎，因此一条记录在被更新后CTID会发生变化（代表了新的版本）。
不管是Oracle还是PostgreSQL，业务层面都不建议使用行号来唯一标识一条记录。因为Oracle的一些操作也可能产生行迁移，导致ROWID变化。
那么有没有一定不变的字段可以用来标识一条记录呢？
PostgreSQL有多种方法，可以实现这一目的，一个生成后就与该ROW绑定，并永恒不变的ID。
PostgreSQL rowid - sequence 唯一标识
```
create table tbl (rowid serial8 not null, c1 int, c2 int);
create unique index idx_tbl_1 on tbl(rowid);
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# select * from tbl;
rowid | c1 | c2
-------+----+----
1 | 1 | 2
2 | 1 | 2
3 | 1 | 2
(3 rows)
```
PostgreSQL rowid - IDENTITY 唯一标识(适用于PostgreSQL 10+)
```
create table tbl (rowid int8 GENERATED ALWAYS AS IDENTITY not null, c1 int, c2 int);
create unique index idx_tbl_1 on tbl(rowid);
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# select * from tbl;
rowid | c1 | c2
-------+----+----
1 | 1 | 2
2 | 1 | 2
3 | 1 | 2
(3 rows)
```
PostgreSQL rowid - oids 唯一标识(oid只有32位，记录数超过40亿的单表，不适用)
postgres=# \dT oid
List of data types
Schema | Name | Description
------------+------+-------------------------------------------
pg_catalog | oid | object identifier(oid), maximum 4 billion
(1 row)
例子
```
postgres=# create table tbl (c1 int, c2 int) with oids;
CREATE TABLE
postgres=# create unique index idx_tbl_oid on tbl(oid);
CREATE INDEX
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16412 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16413 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16414 1
postgres=# select oid,* from tbl;
oid | c1 | c2
-------+----+----
16412 | 1 | 2
16413 | 1 | 2
16414 | 1 | 2
(3 rows)
```
PostgreSQL商用发行版本EDB的rowid功能
EDB的最大特点就是ORACLE兼容性, ROWID不在话下，使用的是OID来实现的ROWID。
3.1.3.11.7 default_with_rowids
Parameter Type: Boolean
Default Value: false
Range: {true | false}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Session user
When set to on, CREATE TABLE includes a ROWID column in newly created tables, which can then be referenced in SQL commands.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
