Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.