diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:17:33 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:17:33 +0000 |
commit | 5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch) | |
tree | 739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/datatype-oid.html | |
parent | Initial commit. (diff) | |
download | postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip |
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/datatype-oid.html')
-rw-r--r-- | doc/src/sgml/html/datatype-oid.html | 166 |
1 files changed, 166 insertions, 0 deletions
diff --git a/doc/src/sgml/html/datatype-oid.html b/doc/src/sgml/html/datatype-oid.html new file mode 100644 index 0000000..8e20ff7 --- /dev/null +++ b/doc/src/sgml/html/datatype-oid.html @@ -0,0 +1,166 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.19. Object Identifier Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="domains.html" title="8.18. Domain Types" /><link rel="next" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.19. Object Identifier Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="domains.html" title="8.18. Domain Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type">Next</a></td></tr></table><hr /></div><div class="sect1" id="DATATYPE-OID"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.19. Object Identifier Types</h2></div></div></div><a id="id-1.5.7.27.2" class="indexterm"></a><a id="id-1.5.7.27.3" class="indexterm"></a><a id="id-1.5.7.27.4" class="indexterm"></a><a id="id-1.5.7.27.5" class="indexterm"></a><a id="id-1.5.7.27.6" class="indexterm"></a><a id="id-1.5.7.27.7" class="indexterm"></a><a id="id-1.5.7.27.8" class="indexterm"></a><a id="id-1.5.7.27.9" class="indexterm"></a><a id="id-1.5.7.27.10" class="indexterm"></a><a id="id-1.5.7.27.11" class="indexterm"></a><a id="id-1.5.7.27.12" class="indexterm"></a><a id="id-1.5.7.27.13" class="indexterm"></a><a id="id-1.5.7.27.14" class="indexterm"></a><a id="id-1.5.7.27.15" class="indexterm"></a><a id="id-1.5.7.27.16" class="indexterm"></a><a id="id-1.5.7.27.17" class="indexterm"></a><a id="id-1.5.7.27.18" class="indexterm"></a><p> + Object identifiers (OIDs) are used internally by + <span class="productname">PostgreSQL</span> as primary keys for various + system tables. + Type <code class="type">oid</code> represents an object identifier. There are also + several alias types for <code class="type">oid</code>, each + named <code class="type">reg<em class="replaceable"><code>something</code></em></code>. + <a class="xref" href="datatype-oid.html#DATATYPE-OID-TABLE" title="Table 8.26. Object Identifier Types">Table 8.26</a> shows an + overview. + </p><p> + The <code class="type">oid</code> type is currently implemented as an unsigned + four-byte integer. Therefore, it is not large enough to provide + database-wide uniqueness in large databases, or even in large + individual tables. + </p><p> + The <code class="type">oid</code> type itself has few operations beyond comparison. + It can be cast to integer, however, and then manipulated using the + standard integer operators. (Beware of possible + signed-versus-unsigned confusion if you do this.) + </p><p> + The OID alias types have no operations of their own except + for specialized input and output routines. These routines are able + to accept and display symbolic names for system objects, rather than + the raw numeric value that type <code class="type">oid</code> would use. The alias + types allow simplified lookup of OID values for objects. For example, + to examine the <code class="structname">pg_attribute</code> rows related to a table + <code class="literal">mytable</code>, one could write: +</p><pre class="programlisting"> +SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass; +</pre><p> + rather than: +</p><pre class="programlisting"> +SELECT * FROM pg_attribute + WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable'); +</pre><p> + While that doesn't look all that bad by itself, it's still oversimplified. + A far more complicated sub-select would be needed to + select the right OID if there are multiple tables named + <code class="literal">mytable</code> in different schemas. + The <code class="type">regclass</code> input converter handles the table lookup according + to the schema path setting, and so it does the <span class="quote">“<span class="quote">right thing</span>”</span> + automatically. Similarly, casting a table's OID to + <code class="type">regclass</code> is handy for symbolic display of a numeric OID. + </p><div class="table" id="DATATYPE-OID-TABLE"><p class="title"><strong>Table 8.26. Object Identifier Types</strong></p><div class="table-contents"><table class="table" summary="Object Identifier Types" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>References</th><th>Description</th><th>Value Example</th></tr></thead><tbody><tr><td><code class="type">oid</code></td><td>any</td><td>numeric object identifier</td><td><code class="literal">564182</code></td></tr><tr><td><code class="type">regclass</code></td><td><code class="structname">pg_class</code></td><td>relation name</td><td><code class="literal">pg_type</code></td></tr><tr><td><code class="type">regcollation</code></td><td><code class="structname">pg_collation</code></td><td>collation name</td><td><code class="literal">"POSIX"</code></td></tr><tr><td><code class="type">regconfig</code></td><td><code class="structname">pg_ts_config</code></td><td>text search configuration</td><td><code class="literal">english</code></td></tr><tr><td><code class="type">regdictionary</code></td><td><code class="structname">pg_ts_dict</code></td><td>text search dictionary</td><td><code class="literal">simple</code></td></tr><tr><td><code class="type">regnamespace</code></td><td><code class="structname">pg_namespace</code></td><td>namespace name</td><td><code class="literal">pg_catalog</code></td></tr><tr><td><code class="type">regoper</code></td><td><code class="structname">pg_operator</code></td><td>operator name</td><td><code class="literal">+</code></td></tr><tr><td><code class="type">regoperator</code></td><td><code class="structname">pg_operator</code></td><td>operator with argument types</td><td><code class="literal">*(integer,integer)</code> + or <code class="literal">-(NONE,integer)</code></td></tr><tr><td><code class="type">regproc</code></td><td><code class="structname">pg_proc</code></td><td>function name</td><td><code class="literal">sum</code></td></tr><tr><td><code class="type">regprocedure</code></td><td><code class="structname">pg_proc</code></td><td>function with argument types</td><td><code class="literal">sum(int4)</code></td></tr><tr><td><code class="type">regrole</code></td><td><code class="structname">pg_authid</code></td><td>role name</td><td><code class="literal">smithee</code></td></tr><tr><td><code class="type">regtype</code></td><td><code class="structname">pg_type</code></td><td>data type name</td><td><code class="literal">integer</code></td></tr></tbody></table></div></div><br class="table-break" /><p> + All of the OID alias types for objects that are grouped by namespace + accept schema-qualified names, and will + display schema-qualified names on output if the object would not + be found in the current search path without being qualified. + For example, <code class="literal">myschema.mytable</code> is acceptable input + for <code class="type">regclass</code> (if there is such a table). That value + might be output as <code class="literal">myschema.mytable</code>, or + just <code class="literal">mytable</code>, depending on the current search path. + The <code class="type">regproc</code> and <code class="type">regoper</code> alias types will only + accept input names that are unique (not overloaded), so they are + of limited use; for most uses <code class="type">regprocedure</code> or + <code class="type">regoperator</code> are more appropriate. For <code class="type">regoperator</code>, + unary operators are identified by writing <code class="literal">NONE</code> for the unused + operand. + </p><p> + The input functions for these types allow whitespace between tokens, + and will fold upper-case letters to lower case, except within double + quotes; this is done to make the syntax rules similar to the way + object names are written in SQL. Conversely, the output functions + will use double quotes if needed to make the output be a valid SQL + identifier. For example, the OID of a function + named <code class="literal">Foo</code> (with upper case <code class="literal">F</code>) + taking two integer arguments could be entered as + <code class="literal">' "Foo" ( int, integer ) '::regprocedure</code>. The + output would look like <code class="literal">"Foo"(integer,integer)</code>. + Both the function name and the argument type names could be + schema-qualified, too. + </p><p> + Many built-in <span class="productname">PostgreSQL</span> functions accept + the OID of a table, or another kind of database object, and for + convenience are declared as taking <code class="type">regclass</code> (or the + appropriate OID alias type). This means you do not have to look up + the object's OID by hand, but can just enter its name as a string + literal. For example, the <code class="function">nextval(regclass)</code> function + takes a sequence relation's OID, so you could call it like this: +</p><pre class="programlisting"> +nextval('foo') <em class="lineannotation"><span class="lineannotation">operates on sequence <code class="literal">foo</code></span></em> +nextval('FOO') <em class="lineannotation"><span class="lineannotation">same as above</span></em> +nextval('"Foo"') <em class="lineannotation"><span class="lineannotation">operates on sequence <code class="literal">Foo</code></span></em> +nextval('myschema.foo') <em class="lineannotation"><span class="lineannotation">operates on <code class="literal">myschema.foo</code></span></em> +nextval('"myschema".foo') <em class="lineannotation"><span class="lineannotation">same as above</span></em> +nextval('foo') <em class="lineannotation"><span class="lineannotation">searches search path for <code class="literal">foo</code></span></em> +</pre><p> + </p><div class="note"><h3 class="title">Note</h3><p> + When you write the argument of such a function as an unadorned + literal string, it becomes a constant of type <code class="type">regclass</code> + (or the appropriate type). + Since this is really just an OID, it will track the originally + identified object despite later renaming, schema reassignment, + etc. This <span class="quote">“<span class="quote">early binding</span>”</span> behavior is usually desirable for + object references in column defaults and views. But sometimes you might + want <span class="quote">“<span class="quote">late binding</span>”</span> where the object reference is resolved + at run time. To get late-binding behavior, force the constant to be + stored as a <code class="type">text</code> constant instead of <code class="type">regclass</code>: +</p><pre class="programlisting"> +nextval('foo'::text) <em class="lineannotation"><span class="lineannotation"><code class="literal">foo</code> is looked up at runtime</span></em> +</pre><p> + The <code class="function">to_regclass()</code> function and its siblings + can also be used to perform run-time lookups. See + <a class="xref" href="functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE" title="Table 9.71. System Catalog Information Functions">Table 9.71</a>. + </p></div><p> + Another practical example of use of <code class="type">regclass</code> + is to look up the OID of a table listed in + the <code class="literal">information_schema</code> views, which don't supply + such OIDs directly. One might for example wish to call + the <code class="function">pg_relation_size()</code> function, which requires + the table OID. Taking the above rules into account, the correct way + to do that is +</p><pre class="programlisting"> +SELECT table_schema, table_name, + pg_relation_size((quote_ident(table_schema) || '.' || + quote_ident(table_name))::regclass) +FROM information_schema.tables +WHERE ... +</pre><p> + The <code class="function">quote_ident()</code> function will take care of + double-quoting the identifiers where needed. The seemingly easier +</p><pre class="programlisting"> +SELECT pg_relation_size(table_name) +FROM information_schema.tables +WHERE ... +</pre><p> + is <span class="emphasis"><em>not recommended</em></span>, because it will fail for + tables that are outside your search path or have names that require + quoting. + </p><p> + An additional property of most of the OID alias types is the creation of + dependencies. If a + constant of one of these types appears in a stored expression + (such as a column default expression or view), it creates a dependency + on the referenced object. For example, if a column has a default + expression <code class="literal">nextval('my_seq'::regclass)</code>, + <span class="productname">PostgreSQL</span> + understands that the default expression depends on the sequence + <code class="literal">my_seq</code>, so the system will not let the sequence + be dropped without first removing the default expression. The + alternative of <code class="literal">nextval('my_seq'::text)</code> does not + create a dependency. + (<code class="type">regrole</code> is an exception to this property. Constants of this + type are not allowed in stored expressions.) + </p><p> + Another identifier type used by the system is <code class="type">xid</code>, or transaction + (abbreviated <abbr class="abbrev">xact</abbr>) identifier. This is the data type of the system columns + <code class="structfield">xmin</code> and <code class="structfield">xmax</code>. Transaction identifiers are 32-bit quantities. + In some contexts, a 64-bit variant <code class="type">xid8</code> is used. Unlike + <code class="type">xid</code> values, <code class="type">xid8</code> values increase strictly + monotonically and cannot be reused in the lifetime of a database cluster. + </p><p> + A third identifier type used by the system is <code class="type">cid</code>, or + command identifier. This is the data type of the system columns + <code class="structfield">cmin</code> and <code class="structfield">cmax</code>. Command identifiers are also 32-bit quantities. + </p><p> + A final identifier type used by the system is <code class="type">tid</code>, or tuple + identifier (row identifier). This is the data type of the system column + <code class="structfield">ctid</code>. A tuple ID is a pair + (block number, tuple index within block) that identifies the + physical location of the row within its table. + </p><p> + (The system columns are further explained in <a class="xref" href="ddl-system-columns.html" title="5.5. System Columns">Section 5.5</a>.) + </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="domains.html" title="8.18. Domain Types">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype-pg-lsn.html" title="8.20. pg_lsn Type">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.18. Domain Types </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.20. <code class="type">pg_lsn</code> Type</td></tr></table></div></body></html>
\ No newline at end of file |