Shulou(Shulou.com)05/31 Report--

I believe many inexperienced people don't know what to do about the hidden fields in the PostgreSQL table. therefore, this paper summarizes the causes and solutions of the problem. I hope you can solve this problem through this article.

1. Implied fields existing in PostgreSQL table

Oid: the object identifier of the row (object ID).

Note: this field appears only when WITH OIDS is used when the table is created, or when the configuration parameter default_with_oids is set. This field is of type oid (with the same name as the field). OID is a 32-bit quantity assigned on a common counter within the same cluster. For a large or long-term database, this counter is likely to overlap. Therefore, OID is the only one that is very wrong.

Tableoid: the OID of the table that contains the row. This field is particularly useful for queries selected from the inheritance hierarchy, because without it, it is difficult to tell which separate table a row comes from. Tableoid can be connected with the oid field of pg_class to get the table name.

Xmin: the identity of the transaction that inserted the version of the row (transaction ID).

Cmin: the command identification (zero-based) inside the insert transaction.

Xmax: delete the identity of the transaction (transaction ID), if not the deleted row version, then zero.

Cmax: the command identifier within the delete transaction, or zero.

Ctid: the physical location of a row version within the table in which it is located

Note that although ctid can be used to locate row versions very quickly, the ctid of a row is updated or moved after each VACUUM FULL. Therefore, ctid cannot be used as a long-term line identifier.

The field information of the data table is saved in the pg_attributed table, and the data table is saved in the pg_class, so you can combine these two to find all the fields of the corresponding table.

Case study:

Testdb=# create table table_oid (id int, name varchar (64)) with (oids=true); create a table with oid

Perform a find operation

Testdb=# select oid, tableoid, tableoid::regclass as tablename, cmax, cmin, xmin, xmax, ctid, * from table_oid

After reading the above, have you mastered the method of what the implicit fields in the PostgreSQL table are? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.