PostgreSQL Oracle 兼容性之 - rowid (CREATE TABLE WITH OIDS)

背景

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.

digoal’s 大量PostgreSQL文章入口