PostgreSQL ctid 与 Oracle rowid 的区别

搞过Oracle的一定对rowid比较有关系啦，由下面的基本组成组成

SELECT ROWID,

DBMS_ROWID.ROWID_OBJECT(ROWID) AS OBJECT,

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FILENUM,

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK,

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROWN

FROM emp;

ROWID OBJECT FILENUM BLOCK ROWN

------------------ ---------- ---------- ---------- ----------

AAAUIRAABAAAg8pAAA 82449 1 134953 0

AAAUIRAABAAAg8pAAB 82449 1 134953 1

AAAUIRAABAAAg8pAAC 82449 1 134953 2

AAAUIRAABAAAg8pAAD 82449 1 134953 3

AAAUIRAABAAAg8pAAE 82449 1 134953 4

AAAUIRAABAAAg8pAAF 82449 1 134953 5

AAAUIRAABAAAg8pAAG 82449 1 134953 6

AAAUIRAABAAAg8pAAH 82449 1 134953 7

AAAUIRAABAAAg8pAAI 82449 1 134953 8

AAAUIRAABAAAg8pAAJ 82449 1 134953 9

AAAUIRAABAAAg8pAAK 82449 1 134953 10

AAAUIRAABAAAg8pAAL 82449 1 134953 11

AAAUIRAABAAAg8pAAM 82449 1 134953 12

AAAUIRAABAAAg8pAAN 82449 1 134953 13

SQL>select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where OBJECT_NAME='EMP';

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID

-------------------- ---------- --------------

EMP 82449 82449

SQL>alter table emp move;

SQL>select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where OBJECT_NAME='EMP';

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID

-------------------- ---------- --------------

EMP 82449 84675

ROWID OBJECT FILENUM BLOCK ROWN

------------------ ---------- ---------- ---------- ----------

AAAUrDAABAAAhSpAAA 84675 1 136361 0

AAAUrDAABAAAhSpAAB 84675 1 136361 1

AAAUrDAABAAAhSpAAC 84675 1 136361 2

AAAUrDAABAAAhSpAAD 84675 1 136361 3

AAAUrDAABAAAhSpAAE 84675 1 136361 4

AAAUrDAABAAAhSpAAF 84675 1 136361 5

AAAUrDAABAAAhSpAAG 84675 1 136361 6

AAAUrDAABAAAhSpAAH 84675 1 136361 7

AAAUrDAABAAAhSpAAI 84675 1 136361 8

AAAUrDAABAAAhSpAAJ 84675 1 136361 9

AAAUrDAABAAAhSpAAK 84675 1 136361 10

AAAUrDAABAAAhSpAAL 84675 1 136361 11

AAAUrDAABAAAhSpAAM 84675 1 136361 12

AAAUrDAABAAAhSpAAN 84675 1 136361 13

14 rows selected.

可以看到数据对象号和块号变了，如果移到另外的表空间文件号也变了

PostgreSQL：

lightdb@postgres=# select relname,oid,relfilenode from pg_class where relname = 'emp';

relname | oid | relfilenode

---------+-------+-------------

emp | 31396 | 31396

(1 row)

lightdb@postgres=# truncate table emp;

TRUNCATE TABLE

lightdb@postgres=# select relname,oid,relfilenode from pg_class where relname = 'emp';

relname | oid | relfilenode

---------+-------+-------------

emp | 31396 | 31407

(1 row)

可以看到 PostgreSQL 的行为和 Oracle 很呀，oidOracle 的object_id，relfile 的data_object_object_id，类似于逻辑对象，前面是实体对象

看下PostgreSQL的ctid

Vacuum full 之后，移动在块内的物理位置会发生变化，所以 ctid 作为一个长期的键行，数据不能正常使用主来标识行。

ctid 由两个数字组成，第一个数字物理表示块号，第二个表示在物理块中的行号，所以说 PostgreSQL 的 ctid 是表等级唯一的行，而 Oracle 中是整个实例中唯一。

lightdb@postgres=# select ctid,empno from emp;

ctid | empno

--------+-------

(0,1) | 7369

(0,2) | 7499

(0,3) | 7521

(0,4) | 7566

Oracle可以用rowid删除表中重复的数据，那么PostgreSQL同样也是可以的

lightdb@postgres=# select * from t_test;

id

----

1

2

2

3

(4 rows)

delete from t_test where ctid in (

select ctid from

(SELECT ctid, id, count(*) OVER (PARTITION BY id ORDER BY ctid) as cnt

FROM t_test

) where cnt > 1) returning *;