PostgreSQL System Column System Columns

Source: Internet
Author: User
Tags postgresql

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.