This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
PostgreSQL defines a series of system columns in all tables, which are normally invisible to the user (e.g., they will not be shown by queries unless explicitly requested). These columns contain meta-data about the content of the table's rows. Many of these contain data which can help to differentiate between tuples (an individual state of a row) when working with transaction blocks. (See Chapter 5 for more on transactions.)
As a result of these system-defined columns, in addition to the user-defined columns of a table, any inserted row will have values in each of the columns described in Table 3-26 .
Table 3-26. System Columns
Column | Description |
---|---|
oid | Object identifier: the unique object identifier of a row. PostgreSQL automatically adds this 4-byte number to all rows. It is never re-used within the same table. |
tableoid | The table object identifier: the oid of the table that contains a row. The name and oid of a table are related by the pg_class system table. |
xmin | Transaction minimum: the transaction identifier of the inserting transaction of a tuple. |
cmin | Command minimum: the command identifier, starting at 0, associated with the inserting transaction of a tuple. |
xmax | Transaction maximum: the transaction identifier of a tuple's deleting transaction. If a tuple is visible (has not been deleted) this is set to zero. |
cmax | Command maximum: the command identifier associated with the deleting transaction of a tuple. Like xmax , if a tuple is visible, this is set to zero. |
ctid | Tuple identifier: the identifier which describes the physical location of the tuple within the database. A pair of numbers are represented by the ctid : the block number, and tuple index within that block. |
 
Continue to: