3
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
4
<title>5.4.�System Columns</title>
5
<link rel="stylesheet" href="stylesheet.css" type="text/css">
6
<link rev="made" href="pgsql-docs@postgresql.org">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
8
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
9
<link rel="up" href="ddl.html" title="Chapter�5.�Data Definition">
10
<link rel="prev" href="ddl-constraints.html" title="5.3.�Constraints">
11
<link rel="next" href="ddl-alter.html" title="5.5.�Modifying Tables">
12
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
14
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
15
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
16
<a name="ddl-system-columns"></a>5.4.�System Columns</h2></div></div></div>
17
<p> Every table has several <em class="firstterm">system columns</em> that are
18
implicitly defined by the system. Therefore, these names cannot be
19
used as names of user-defined columns. (Note that these
20
restrictions are separate from whether the name is a key word or
21
not; quoting a name will not allow you to escape these
22
restrictions.) You do not really need to be concerned about these
23
columns, just know they exist.
25
<a name="id575741"></a><div class="variablelist"><dl>
26
<dt><span class="term"><code class="structfield">oid</code></span></dt>
27
<dd><p> <a name="id575759"></a>
28
The object identifier (object ID) of a row. This column is only
29
present if the table was created using <code class="literal">WITH
30
OIDS</code>, or if the <a href="runtime-config-compatible.html#guc-default-with-oids">default_with_oids</a>
31
configuration variable was set. This column is of type
32
<code class="type">oid</code> (same name as the column); see <a href="datatype-oid.html" title="8.12.�Object Identifier Types">Section�8.12, “Object Identifier Types”</a> for more information about the type.
34
<dt><span class="term"><code class="structfield">tableoid</code></span></dt>
36
<a name="id575807"></a><p> The OID of the table containing this row. This column is
37
particularly handy for queries that select from inheritance
38
hierarchies (see <a href="ddl-inherit.html" title="5.8.�Inheritance">Section�5.8, “Inheritance”</a>), since without it,
39
it's difficult to tell which individual table a row came from. The
40
<code class="structfield">tableoid</code> can be joined against the
41
<code class="structfield">oid</code> column of
42
<code class="structname">pg_class</code> to obtain the table name.
45
<dt><span class="term"><code class="structfield">xmin</code></span></dt>
47
<a name="id575846"></a><p> The identity (transaction ID) of the inserting transaction for
48
this row version. (A row version is an individual state of a
49
row; each update of a row creates a new row version for the same
53
<dt><span class="term"><code class="structfield">cmin</code></span></dt>
55
<a name="id575865"></a><p> The command identifier (starting at zero) within the inserting
59
<dt><span class="term"><code class="structfield">xmax</code></span></dt>
61
<a name="id575882"></a><p> The identity (transaction ID) of the deleting transaction, or
62
zero for an undeleted row version. It is possible for this column to
63
be nonzero in a visible row version. That usually indicates that the
64
deleting transaction hasn't committed yet, or that an attempted
65
deletion was rolled back.
68
<dt><span class="term"><code class="structfield">cmax</code></span></dt>
70
<a name="id575903"></a><p> The command identifier within the deleting transaction, or zero.
73
<dt><span class="term"><code class="structfield">ctid</code></span></dt>
75
<a name="id575919"></a><p> The physical location of the row version within its table. Note that
76
although the <code class="structfield">ctid</code> can be used to
77
locate the row version very quickly, a row's
78
<code class="structfield">ctid</code> will change each time it is
79
updated or moved by <code class="command">VACUUM FULL</code>. Therefore
80
<code class="structfield">ctid</code> is useless as a long-term row
81
identifier. The OID, or even better a user-defined serial
82
number, should be used to identify logical rows.
86
<p> OIDs are 32-bit quantities and are assigned from a single
87
cluster-wide counter. In a large or long-lived database, it is
88
possible for the counter to wrap around. Hence, it is bad
89
practice to assume that OIDs are unique, unless you take steps to
90
ensure that this is the case. If you need to identify the rows in
91
a table, using a sequence generator is strongly recommended.
92
However, OIDs can be used as well, provided that a few additional
93
precautions are taken:
96
<div class="itemizedlist"><ul type="disc">
97
<li><p> A unique constraint should be created on the OID column of each
98
table for which the OID will be used to identify rows. When such
99
a unique constraint (or unique index) exists, the system takes
100
care not to generate an OID matching an already-existing row.
101
(Of course, this is only possible if the table contains fewer
102
than 2<sup>32</sup> (4 billion) rows, and in practice the
103
table size had better be much less than that, or performance
106
<li><p> OIDs should never be assumed to be unique across tables; use
107
the combination of <code class="structfield">tableoid</code> and row OID if you
108
need a database-wide identifier.
110
<li><p> The tables in question should be created using <code class="literal">WITH
111
OIDS</code>. As of <span class="productname">PostgreSQL</span> 8.1,
112
<code class="literal">WITHOUT OIDS</code> is the default.
117
<p> Transaction identifiers are also 32-bit quantities. In a
118
long-lived database it is possible for transaction IDs to wrap
119
around. This is not a fatal problem given appropriate maintenance
120
procedures; see <a href="maintenance.html" title="Chapter�22.�Routine Database Maintenance Tasks">Chapter�22, <i>Routine Database Maintenance Tasks</i></a> for details. It is
121
unwise, however, to depend on the uniqueness of transaction IDs
122
over the long term (more than one billion transactions).
125
identifiers are also 32-bit quantities. This creates a hard limit
126
of 2<sup>32</sup> (4 billion) <acronym class="acronym">SQL</acronym> commands
127
within a single transaction. In practice this limit is not a
128
problem [mdash ] note that the limit is on number of
129
<acronym class="acronym">SQL</acronym> commands, not number of rows processed.
added
removed
docs/en_US/pg/ddl-system-columns.html
