PostgreSQL system column System Columns
Each table has hidden system columns, which cannot be created with the same system column name. Let's explain which system columns PostgreSQL has.
(1) oid (4 bytes)
Object identifier (i.e. object ID) is mainly used for system tables such as pg_class (a table for recording table) and pg_namespace (a table for recording schema).
When creating a table, if you specify with oids, there is an oid column. It can also be controlled by the parameter default_with_oids, which defaults to off, which means that when a table is built without with oids, there is no oid column.
Eg:
# check the oid corresponding to this record of pg_class
Postgres=# select oid,relname from pg_class where oid='pg_class'::regclass
Oid | relname
-+-
1259 | pg_class
# create a new table
Postgres=# create table T1 (C1 integer,c2 varchar (20)) with oids
CREATE TABLE
Postgres=# insert into T1 select 1 recording aaaaa
INSERT 16456 1
Postgres=# insert into T1 values (2memorialbbb')
INSERT 16457 1
Postgres=#\ d + T1
Table "public.t1"
Column | Type | Modifiers | Storage | Stats target | Description
-+
C1 | integer | | plain | |
C2 | character varying (20) | | extended | |
Has OIDs: yes
Postgres=# select oid,c1,c2 from t1
Oid | C1 | c2
-+-
16456 | 1 | aaa
16457 | 2 | bbb
(2) tableid (4 bytes)
A unique identifier of the table object. A table corresponds to only one tableoid. You can join tableoid with the oid column of pgclass to get the table name.
Postgres=# select oid,tableoid from t1
Oid | tableoid
-+-
16456 | 16453
16457 | 16453
16458 | 16453
Postgres=# select tableoid from t2
Tableoid
-
16464
Postgres=# select oid,relname from pg_class
Oid | relname
-+-
16453 | T1
16464 | T2
Postgres=# select relname from pg_class where oid in (16453 and 16464)
Relname
-
T1
T2
(3) ctid (6 bytes)
A physical location identifier in a table, similar to the rowid of oracle, except that the value may change when the table is vacuum full or the row value is update. Therefore, it is better to define the uniqueness of the table value by creating a primary key column of the sequence value to identify it.
(4) xmin
Is the inserted transaction identifier transaction ID, which is used to identify a version control under different transactions. This value is changed each time the row is updated. It can be combined with mvcc version.
(5) xmax
Is the transaction identifier transaction ID that deletes the update, and if the value is not 0, the row of data is not currently committed or rolled back. For example, when you set up a begin...commit transaction, you can clearly see the change in this value.
(6) cmin
Insert the command identifier command identifier for the transaction, starting at 0
(7) cmax
Delete the command identifier of the transaction command identifier, or 0
Eg:
Postgres=# create table T1 (C1 integer,c2 varchar (20))
Postgres=# insert into T1 select generate_series (1p3), repeat ('hello',2)
# the xmin of three lines of records is the same, indicating the same thing
Postgres=# select cmin,cmax,xmin,xmax,ctid,* from t1
Cmin | cmax | xmin | xmax | ctid | C1 | c2
-+-
0 | 0 | 1806 | 0 | (0Power1) | 1 | hellohello
0 | 0 | 1806 | 0 | (0Power2) | 2 | hellohello
0 | 0 | 1806 | 0 | (0Power3) | 3 | hellohello
Begin
Insert into T1 values (4 recorder aaaaa')
Insert into T1 values (5 recording bbbb')
Insert into T1 values (6 recorder ccc')
Commit
# the xmin of the fourth, fifth and sixth lines is different, indicating different things, and cmin and cmax have also changed
Postgres=# select cmin,cmax,xmin,xmax,ctid,* from t1
Cmin | cmax | xmin | xmax | ctid | C1 | c2
-+-
0 | 0 | 1806 | 0 | (0Power1) | 1 | hellohello
0 | 0 | 1806 | 0 | (0Power2) | 2 | hellohello
0 | 0 | 1806 | 0 | (0Power3) | 3 | hellohello
0 | 0 | 1807 | 0 | (0Power4) | 4 | aaa
1 | 1 | 1807 | 0 | (0Power5) | 5 | bbb
2 | 2 | 1807 | 0 | (0Power6) | 6 | ccc
Session1:
Postgres=# begin
Postgres=# update T1 set c2 stores cdhu' where C1 5
Postgres=# update T1 set c2 where cdhucdhu6
# ctid has changed at this time:
Postgres=# select cmin,cmax,xmin,xmax,ctid,* from t1
Cmin | cmax | xmin | xmax | ctid | C1 | c2
-+-
0 | 0 | 1806 | 0 | (0Power1) | 1 | hellohello
0 | 0 | 1806 | 0 | (0Power2) | 2 | hellohello
0 | 0 | 1806 | 0 | (0Power3) | 3 | hellohello
0 | 0 | 1807 | 0 | (0Power4) | 4 | aaa
0 | 0 | 1808 | 0 | (0Power7) | 5 | cdhu
1 | 1 | 1808 | 0 | (0Power8) | 6 | cdhucdhu
(6 rows)
Open another conversation.
Session2:
# the above update transaction is not finished yet, so xmax is not 0:
Postgres=# select cmin,cmax,xmin,xmax,ctid,* from t1
Cmin | cmax | xmin | xmax | ctid | C1 | c2
-+-
0 | 0 | 1806 | 0 | (0Power1) | 1 | hellohello
0 | 0 | 1806 | 0 | (0Power2) | 2 | hellohello
0 | 0 | 1806 | 0 | (0Power3) | 3 | hellohello
0 | 0 | 1807 | 0 | (0Power4) | 4 | aaa
0 | 0 | 1807 | 1808 | (0Power5) | 5 | bbb
1 | 1 | 1807 | 1808 | (0Power6) | 6 | ccc
Session1:
Postgres=# commit
Session2:
Postgres=# select cmin,cmax,xmin,xmax,ctid,* from t1
Cmin | cmax | xmin | xmax | ctid | C1 | c2
-+-
0 | 0 | 1806 | 0 | (0Power1) | 1 | hellohello
0 | 0 | 1806 | 0 | (0Power2) | 2 | hellohello
0 | 0 | 1806 | 0 | (0Power3) | 3 | hellohello
0 | 0 | 1807 | 0 | (0Power4) | 4 | aaa
0 | 0 | 1808 | 0 | (0Power7) | 5 | cdhu
1 | 1 | 1808 | 0 | (0Power8) | 6 | cdhucdhu
