PostgreSQL System Column System Columns

Each table has a hidden system column, which cannot be the same as the system column name when creating a table column, and the following are some of the system columns that PostgreSQL has.

(1) OID (4 bytes)

The object identifier (that is, the object ID) is used primarily for system tables such as pg_class (a table of tables is recorded), Pg_namespace (a table that records the schema),

When you create a table, an OID column exists if you specify with OIDs. It can also be controlled by the parameter default_with_oids, which is off by default, indicating that there is no OID column when a table is not added with OIDs.

eg

#查看pg_class这个条记录对应的oid

postgres=# Select Oid,relname from Pg_class where oid= ' Pg_class ':: Regclass;

OID | Relname

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

1259 | Pg_class

#创建一个新的表

postgres=# CREATE TABLE T1 (C1 integer,c2 varchar) with OIDs;

CREATE TABLE

postgres=# INSERT INTO T1 Select 1, ' AAA ';

INSERT 16456 1

postgres=# INSERT INTO T1 values (2, ' BBB ');

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 for the Table object, a table that corresponds to only one tableoid, which can be concatenated with the OID column of the 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 OIDs in (16453,16464);

Relname

---------

T1

T2

(3) Ctid (6 bytes)

A physical location identifier in the table is similar to Oracle's ROWID, but with a different point, the value may change when the table is vacuum full or the row value is update. Therefore, it is best to define the uniqueness of a table value by creating a primary key column of a sequence value to identify the appropriate

(4) Xmin

is the transaction ID of the inserted transaction identifier, which is used to identify a version control under different transactions. This value will be changed every time the row is updated. Can be combined with the MVCC version to see

(5) Xmax

is to delete the updated transaction identifier transaction ID, and if the value is not 0, the row data is not currently committed or rolled back. For example, when setting up a begin...commit transaction, you can see that the value changes significantly

(6) Cmin

Command identifier for the insert transaction, identifier, starting from 0

(7) Cmax

The command identifier for the delete transaction is identifier, or 0

eg

postgres=# CREATE TABLE T1 (C1 integer,c2 varchar (20));

postgres=# INSERT INTO T1 select Generate_series (1,3), repeat (' Hello ', 2);

#三行记录的xmin一样 means the same thing.

postgres=# select cmin,cmax,xmin,xmax,ctid,* from T1;

Cmin | Cmax | xmin | Xmax | Ctid |     C1 | C2

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

0 | 0 |    1806 | 0 |  (0,1) | 1 | Hellohello

0 | 0 |    1806 | 0 |  (0,2) | 2 | Hellohello

0 | 0 |    1806 | 0 |  (0,3) | 3 | Hellohello

Begin

INSERT into T1 values (4, ' AAA ');

INSERT into T1 values (5, ' BBB ');

INSERT into T1 values (6, ' CCC ');

Commit

#第四行, line fifth, line sixth xmin different, mean different things, cmin and Cmax also have changed

postgres=# select cmin,cmax,xmin,xmax,ctid,* from T1;

Cmin | Cmax | xmin | Xmax | Ctid |     C1 | C2

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

0 | 0 |    1806 | 0 |  (0,1) | 1 | Hellohello

0 | 0 |    1806 | 0 |  (0,2) | 2 | Hellohello

0 | 0 |    1806 | 0 |  (0,3) | 3 | Hellohello

0 | 0 |    1807 | 0 |  (0,4) | 4 | Aaa

1 | 1 |    1807 | 0 |  (0,5) | 5 | Bbb

2 | 2 |    1807 | 0 |  (0,6) | 6 | Ccc

Session1:

postgres=# begin;

postgres=# Update T1 set c2= ' Cdhu ' where c1=5;

postgres=# Update T1 set c2= ' Cdhucdhu ' where c1=6;

#此时的ctid变化了:

postgres=# select cmin,cmax,xmin,xmax,ctid,* from T1;

Cmin | Cmax | xmin | Xmax | Ctid |     C1 | C2

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

0 | 0 |    1806 | 0 |  (0,1) | 1 | Hellohello

0 | 0 |    1806 | 0 |  (0,2) | 2 | Hellohello

0 | 0 |    1806 | 0 |  (0,3) | 3 | Hellohello

0 | 0 |    1807 | 0 |  (0,4) | 4 | Aaa

0 | 0 |    1808 | 0 |  (0,7) | 5 | Cdhu

1 | 1 |    1808 | 0 |  (0,8) | 6 | Cdhucdhu

(6 rows)

Open a session again

Session2:

#上面update事物还没有结束, so Xmax is now not for 0:

postgres=# select cmin,cmax,xmin,xmax,ctid,* from T1;

Cmin | Cmax | xmin | Xmax | Ctid |     C1 | C2

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

0 | 0 |    1806 | 0 |  (0,1) | 1 | Hellohello

0 | 0 |    1806 | 0 |  (0,2) | 2 | Hellohello

0 | 0 |    1806 | 0 |  (0,3) | 3 | Hellohello

0 | 0 |    1807 | 0 |  (0,4) | 4 | Aaa

0 | 0 | 1807 | 1808 |  (0,5) | 5 | Bbb

1 | 1 | 1807 | 1808 |  (0,6) | 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 |  (0,1) | 1 | Hellohello

0 | 0 |    1806 | 0 |  (0,2) | 2 | Hellohello

0 | 0 |    1806 | 0 |  (0,3) | 3 | Hellohello

0 | 0 |    1807 | 0 |  (0,4) | 4 | Aaa

0 | 0 |    1808 | 0 |  (0,7) | 5 | Cdhu

1 | 1 |    1808 | 0 |  (0,8) | 6 | Cdhucdhu

This article is from the "10979687" blog, please be sure to keep this source http://10989687.blog.51cto.com/10979687/1972372

PostgreSQL System Column System Columns