diff options
Diffstat (limited to 'doc/src/sgml/html/system-catalog-initial-data.html')
| -rw-r--r-- | doc/src/sgml/html/system-catalog-initial-data.html | 379 |
1 files changed, 379 insertions, 0 deletions
diff --git a/doc/src/sgml/html/system-catalog-initial-data.html b/doc/src/sgml/html/system-catalog-initial-data.html new file mode 100644 index 0000000..3b67dab --- /dev/null +++ b/doc/src/sgml/html/system-catalog-initial-data.html @@ -0,0 +1,379 @@ +<?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>70.2. System Catalog Initial Data</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 V1.79.1" /><link rel="prev" href="system-catalog-declarations.html" title="70.1. System Catalog Declaration Rules" /><link rel="next" href="bki-format.html" title="70.3. BKI File Format" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">70.2. System Catalog Initial Data</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="system-catalog-declarations.html" title="70.1. System Catalog Declaration Rules">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="bki.html" title="Chapter 70. System Catalog Declarations and Initial Contents">Up</a></td><th width="60%" align="center">Chapter 70. System Catalog Declarations and Initial Contents</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="bki-format.html" title="70.3. BKI File Format">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="SYSTEM-CATALOG-INITIAL-DATA"><div class="titlepage"><div><div><h2 class="title" style="clear: both">70.2. System Catalog Initial Data</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-INITIAL-DATA-FORMAT">70.2.1. Data File Format</a></span></dt><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-OID-ASSIGNMENT">70.2.2. OID Assignment</a></span></dt><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-OID-REFERENCES">70.2.3. OID Reference Lookup</a></span></dt><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-AUTO-ARRAY-TYPES">70.2.4. Automatic Creation of Array Types</a></span></dt><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-RECIPES">70.2.5. Recipes for Editing Data Files</a></span></dt></dl></div><p> + Each catalog that has any manually-created initial data (some do not) + has a corresponding <code class="literal">.dat</code> file that contains its + initial data in an editable format. + </p><div class="sect2" id="SYSTEM-CATALOG-INITIAL-DATA-FORMAT"><div class="titlepage"><div><div><h3 class="title">70.2.1. Data File Format</h3></div></div></div><p> + Each <code class="literal">.dat</code> file contains Perl data structure literals + that are simply eval'd to produce an in-memory data structure consisting + of an array of hash references, one per catalog row. + A slightly modified excerpt from <code class="filename">pg_database.dat</code> + will demonstrate the key features: + </p><pre class="programlisting"> +[ + +# A comment could appear here. +{ oid => '1', oid_symbol => 'TemplateDbOid', + descr => 'database\'s default template', + datname => 'template1', encoding => 'ENCODING', datcollate => 'LC_COLLATE', + datctype => 'LC_CTYPE', datistemplate => 't', datallowconn => 't', + datconnlimit => '-1', datlastsysoid => '0', datfrozenxid => '0', + datminmxid => '1', dattablespace => 'pg_default', datacl => '_null_' }, + +] +</pre><p> + Points to note: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + The overall file layout is: open square bracket, one or more sets of + curly braces each of which represents a catalog row, close square + bracket. Write a comma after each closing curly brace. + </p></li><li class="listitem"><p> + Within each catalog row, write comma-separated + <em class="replaceable"><code>key</code></em> <code class="literal">=></code> + <em class="replaceable"><code>value</code></em> pairs. The + allowed <em class="replaceable"><code>key</code></em>s are the names of the catalog's + columns, plus the metadata keys <code class="literal">oid</code>, + <code class="literal">oid_symbol</code>, + <code class="literal">array_type_oid</code>, and <code class="literal">descr</code>. + (The use of <code class="literal">oid</code> and <code class="literal">oid_symbol</code> + is described in <a class="xref" href="system-catalog-initial-data.html#SYSTEM-CATALOG-OID-ASSIGNMENT" title="70.2.2. OID Assignment">Section 70.2.2</a> below, + while <code class="literal">array_type_oid</code> is described in + <a class="xref" href="system-catalog-initial-data.html#SYSTEM-CATALOG-AUTO-ARRAY-TYPES" title="70.2.4. Automatic Creation of Array Types">Section 70.2.4</a>. + <code class="literal">descr</code> supplies a description string for the object, + which will be inserted into <code class="structname">pg_description</code> + or <code class="structname">pg_shdescription</code> as appropriate.) + While the metadata keys are optional, the catalog's defined columns + must all be provided, except when the catalog's <code class="literal">.h</code> + file specifies a default value for the column. + (In the example above, the <code class="structfield">datdba</code> field has + been omitted because <code class="filename">pg_database.h</code> supplies a + suitable default value for it.) + </p></li><li class="listitem"><p> + All values must be single-quoted. Escape single quotes used within a + value with a backslash. Backslashes meant as data can, but need not, + be doubled; this follows Perl's rules for simple quoted literals. + Note that backslashes appearing as data will be treated as escapes by + the bootstrap scanner, according to the same rules as for escape string + constants (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE" title="4.1.2.2. String Constants with C-Style Escapes">Section 4.1.2.2</a>); for + example <code class="literal">\t</code> converts to a tab character. If you + actually want a backslash in the final value, you will need to write + four of them: Perl strips two, leaving <code class="literal">\\</code> for the + bootstrap scanner to see. + </p></li><li class="listitem"><p> + Null values are represented by <code class="literal">_null_</code>. + (Note that there is no way to create a value that is just that + string.) + </p></li><li class="listitem"><p> + Comments are preceded by <code class="literal">#</code>, and must be on their + own lines. + </p></li><li class="listitem"><p> + Field values that are OIDs of other catalog entries should be + represented by symbolic names rather than actual numeric OIDs. + (In the example above, <code class="structfield">dattablespace</code> + contains such a reference.) + This is described in <a class="xref" href="system-catalog-initial-data.html#SYSTEM-CATALOG-OID-REFERENCES" title="70.2.3. OID Reference Lookup">Section 70.2.3</a> + below. + </p></li><li class="listitem"><p> + Since hashes are unordered data structures, field order and line + layout aren't semantically significant. However, to maintain a + consistent appearance, we set a few rules that are applied by the + formatting script <code class="filename">reformat_dat_file.pl</code>: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p> + Within each pair of curly braces, the metadata + fields <code class="literal">oid</code>, <code class="literal">oid_symbol</code>, + <code class="literal">array_type_oid</code>, and <code class="literal">descr</code> + (if present) come first, in that order, then the catalog's own + fields appear in their defined order. + </p></li><li class="listitem"><p> + Newlines are inserted between fields as needed to limit line length + to 80 characters, if possible. A newline is also inserted between + the metadata fields and the regular fields. + </p></li><li class="listitem"><p> + If the catalog's <code class="literal">.h</code> file specifies a default + value for a column, and a data entry has that same + value, <code class="filename">reformat_dat_file.pl</code> will omit it from + the data file. This keeps the data representation compact. + </p></li><li class="listitem"><p> + <code class="filename">reformat_dat_file.pl</code> preserves blank lines + and comment lines as-is. + </p></li></ul></div><p> + + It's recommended to run <code class="filename">reformat_dat_file.pl</code> + before submitting catalog data patches. For convenience, you can + simply change to <code class="filename">src/include/catalog/</code> and + run <code class="literal">make reformat-dat-files</code>. + </p></li><li class="listitem"><p> + If you want to add a new method of making the data representation + smaller, you must implement it + in <code class="filename">reformat_dat_file.pl</code> and also + teach <code class="function">Catalog::ParseData()</code> how to expand the + data back into the full representation. + </p></li></ul></div></div><div class="sect2" id="SYSTEM-CATALOG-OID-ASSIGNMENT"><div class="titlepage"><div><div><h3 class="title">70.2.2. OID Assignment</h3></div></div></div><p> + A catalog row appearing in the initial data can be given a + manually-assigned OID by writing an <code class="literal">oid + => <em class="replaceable"><code>nnnn</code></em></code> metadata field. + Furthermore, if an OID is assigned, a C macro for that OID can be + created by writing an <code class="literal">oid_symbol + => <em class="replaceable"><code>name</code></em></code> metadata field. + </p><p> + Pre-loaded catalog rows must have preassigned OIDs if there are OID + references to them in other pre-loaded rows. A preassigned OID is + also needed if the row's OID must be referenced from C code. + If neither case applies, the <code class="literal">oid</code> metadata field can + be omitted, in which case the bootstrap code assigns an OID + automatically. + In practice we usually preassign OIDs for all or none of the pre-loaded + rows in a given catalog, even if only some of them are actually + cross-referenced. + </p><p> + Writing the actual numeric value of any OID in C code is considered + very bad form; always use a macro, instead. Direct references + to <code class="structname">pg_proc</code> OIDs are common enough that there's + a special mechanism to create the necessary macros automatically; + see <code class="filename">src/backend/utils/Gen_fmgrtab.pl</code>. Similarly + — but, for historical reasons, not done the same way — + there's an automatic method for creating macros + for <code class="structname">pg_type</code> + OIDs. <code class="literal">oid_symbol</code> entries are therefore not + necessary in those two catalogs. Likewise, macros for + the <code class="structname">pg_class</code> OIDs of system catalogs and + indexes are set up automatically. For all other system catalogs, you + have to manually specify any macros you need + via <code class="literal">oid_symbol</code> entries. + </p><p> + To find an available OID for a new pre-loaded row, run the + script <code class="filename">src/include/catalog/unused_oids</code>. + It prints inclusive ranges of unused OIDs (e.g., the output + line <code class="literal">45-900</code> means OIDs 45 through 900 have not been + allocated yet). Currently, OIDs 1–9999 are reserved for manual + assignment; the <code class="filename">unused_oids</code> script simply looks + through the catalog headers and <code class="filename">.dat</code> files + to see which ones do not appear. You can also use + the <code class="filename">duplicate_oids</code> script to check for mistakes. + (<code class="filename">genbki.pl</code> will assign OIDs for any rows that + didn't get one hand-assigned to them, and it will also detect duplicate + OIDs at compile time.) + </p><p> + When choosing OIDs for a patch that is not expected to be committed + immediately, best practice is to use a group of more-or-less + consecutive OIDs starting with some random choice in the range + 8000—9999. This minimizes the risk of OID collisions with other + patches being developed concurrently. To keep the 8000—9999 + range free for development purposes, after a patch has been committed + to the master git repository its OIDs should be renumbered into + available space below that range. Typically, this will be done + near the end of each development cycle, moving all OIDs consumed by + patches committed in that cycle at the same time. The script + <code class="filename">renumber_oids.pl</code> can be used for this purpose. + If an uncommitted patch is found to have OID conflicts with some + recently-committed patch, <code class="filename">renumber_oids.pl</code> may + also be useful for recovering from that situation. + </p><p> + Because of this convention of possibly renumbering OIDs assigned by + patches, the OIDs assigned by a patch should not be considered stable + until the patch has been included in an official release. We do not + change manually-assigned object OIDs once released, however, as that + would create assorted compatibility problems. + </p><p> + If <code class="filename">genbki.pl</code> needs to assign an OID to a catalog + entry that does not have a manually-assigned OID, it will use a value in + the range 10000—11999. The server's OID counter is set to 12000 + at the start of a bootstrap run. Thus objects created by regular SQL + commands during the later phases of bootstrap, such as objects created + while running the <code class="filename">information_schema.sql</code> script, + receive OIDs of 12000 or above. + </p><p> + OIDs assigned during normal database operation are constrained to be + 16384 or higher. This ensures that the range 10000—16383 is free + for OIDs assigned automatically by <code class="filename">genbki.pl</code> or + during bootstrap. These automatically-assigned OIDs are not considered + stable, and may change from one installation to another. + </p></div><div class="sect2" id="SYSTEM-CATALOG-OID-REFERENCES"><div class="titlepage"><div><div><h3 class="title">70.2.3. OID Reference Lookup</h3></div></div></div><p> + In principle, cross-references from one initial catalog row to another + could be written just by writing the preassigned OID of the referenced + row in the referencing field. However, that is against project + policy, because it is error-prone, hard to read, and subject to + breakage if a newly-assigned OID is renumbered. Therefore + <code class="filename">genbki.pl</code> provides mechanisms to write + symbolic references instead. + The rules are as follows: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + Use of symbolic references is enabled in a particular catalog column + by attaching <code class="literal">BKI_LOOKUP(<em class="replaceable"><code>lookuprule</code></em>)</code> + to the column's definition, where <em class="replaceable"><code>lookuprule</code></em> + is the name of the referenced catalog, e.g., <code class="literal">pg_proc</code>. + <code class="literal">BKI_LOOKUP</code> can be attached to columns of + type <code class="type">Oid</code>, <code class="type">regproc</code>, <code class="type">oidvector</code>, + or <code class="type">Oid[]</code>; in the latter two cases it implies performing a + lookup on each element of the array. + </p></li><li class="listitem"><p> + It's also permissible to attach <code class="literal">BKI_LOOKUP(encoding)</code> + to integer columns to reference character set encodings, which are + not currently represented as catalog OIDs, but have a set of values + known to <code class="filename">genbki.pl</code>. + </p></li><li class="listitem"><p> + In such a column, all entries must use the symbolic format except + when writing <code class="literal">0</code> for InvalidOid. (If the column is + declared <code class="type">regproc</code>, you can optionally + write <code class="literal">-</code> instead of <code class="literal">0</code>.) + <code class="filename">genbki.pl</code> will warn about unrecognized names. + </p></li><li class="listitem"><p> + Most kinds of catalog objects are simply referenced by their names. + Note that type names must exactly match the + referenced <code class="structname">pg_type</code> + entry's <code class="structfield">typname</code>; you do not get to use + any aliases such as <code class="literal">integer</code> + for <code class="literal">int4</code>. + </p></li><li class="listitem"><p> + A function can be represented by + its <code class="structfield">proname</code>, if that is unique among + the <code class="filename">pg_proc.dat</code> entries (this works like regproc + input). Otherwise, write it + as <em class="replaceable"><code>proname(argtypename,argtypename,...)</code></em>, + like regprocedure. The argument type names must be spelled exactly as + they are in the <code class="filename">pg_proc.dat</code> entry's + <code class="structfield">proargtypes</code> field. Do not insert any + spaces. + </p></li><li class="listitem"><p> + Operators are represented + by <em class="replaceable"><code>oprname(lefttype,righttype)</code></em>, + writing the type names exactly as they appear in + the <code class="filename">pg_operator.dat</code> + entry's <code class="structfield">oprleft</code> + and <code class="structfield">oprright</code> fields. + (Write <code class="literal">0</code> for the omitted operand of a unary + operator.) + </p></li><li class="listitem"><p> + The names of opclasses and opfamilies are only unique within an + access method, so they are represented + by <em class="replaceable"><code>access_method_name</code></em><code class="literal">/</code><em class="replaceable"><code>object_name</code></em>. + </p></li><li class="listitem"><p> + In none of these cases is there any provision for + schema-qualification; all objects created during bootstrap are + expected to be in the <code class="literal">pg_catalog</code> schema. + </p></li><li class="listitem"><p> + In addition to the generic lookup mechanisms, there is a special + convention that <code class="literal">PGNSP</code> is replaced by the OID of + the <code class="literal">pg_catalog</code> schema, + and <code class="literal">PGUID</code> is replaced by the OID of the bootstrap + superuser role. These usages are somewhat historical but so far + there hasn't been a need to generalize them. + </p></li></ul></div><p> + <code class="filename">genbki.pl</code> resolves all symbolic references while it + runs, and puts simple numeric OIDs into the emitted BKI file. There is + therefore no need for the bootstrap backend to deal with symbolic + references. + </p></div><div class="sect2" id="SYSTEM-CATALOG-AUTO-ARRAY-TYPES"><div class="titlepage"><div><div><h3 class="title">70.2.4. Automatic Creation of Array Types</h3></div></div></div><p> + Most scalar data types should have a corresponding array type (that is, + a standard varlena array type whose element type is the scalar type, and + which is referenced by the <code class="structfield">typarray</code> field of + the scalar type's <code class="structname">pg_type</code> + entry). <code class="filename">genbki.pl</code> is able to generate + the <code class="structname">pg_type</code> entry for the array type + automatically in most cases. + </p><p> + To use this facility, just write an <code class="literal">array_type_oid + => <em class="replaceable"><code>nnnn</code></em></code> metadata field in the + scalar type's <code class="structname">pg_type</code> entry, specifying the OID + to use for the array type. You may then omit + the <code class="structfield">typarray</code> field, since it will be filled + automatically with that OID. + </p><p> + The generated array type's name is the scalar type's name with an + underscore prepended. The array entry's other fields are filled from + <code class="literal">BKI_ARRAY_DEFAULT(<em class="replaceable"><code>value</code></em>)</code> + annotations in <code class="filename">pg_type.h</code>, or if there isn't one, + copied from the scalar type. (There's also a special case + for <code class="structfield">typalign</code>.) Then + the <code class="structfield">typelem</code> + and <code class="structfield">typarray</code> fields of the two entries are + set to cross-reference each other. + </p></div><div class="sect2" id="SYSTEM-CATALOG-RECIPES"><div class="titlepage"><div><div><h3 class="title">70.2.5. Recipes for Editing Data Files</h3></div></div></div><p> + Here are some suggestions about the easiest ways to perform common tasks + when updating catalog data files. + </p><p><strong>Add a new column with a default to a catalog: </strong> + Add the column to the header file with + a <code class="literal">BKI_DEFAULT(<em class="replaceable"><code>value</code></em>)</code> + annotation. The data file need only be adjusted by adding the field + in existing rows where a non-default value is needed. + </p><p><strong>Add a default value to an existing column that doesn't have + one: </strong> + Add a <code class="literal">BKI_DEFAULT</code> annotation to the header file, + then run <code class="literal">make reformat-dat-files</code> to remove + now-redundant field entries. + </p><p><strong>Remove a column, whether it has a default or not: </strong> + Remove the column from the header, then run <code class="literal">make + reformat-dat-files</code> to remove now-useless field entries. + </p><p><strong>Change or remove an existing default value: </strong> + You cannot simply change the header file, since that will cause the + current data to be interpreted incorrectly. First run <code class="literal">make + expand-dat-files</code> to rewrite the data files with all + default values inserted explicitly, then change or remove + the <code class="literal">BKI_DEFAULT</code> annotation, then run <code class="literal">make + reformat-dat-files</code> to remove superfluous fields again. + </p><p><strong>Ad-hoc bulk editing: </strong> + <code class="filename">reformat_dat_file.pl</code> can be adapted to perform + many kinds of bulk changes. Look for its block comments showing where + one-off code can be inserted. In the following example, we are going + to consolidate two boolean fields in <code class="structname">pg_proc</code> + into a char field: + + </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p> + Add the new column, with a default, + to <code class="filename">pg_proc.h</code>: +</p><pre class="programlisting"> ++ /* see PROKIND_ categories below */ ++ char prokind BKI_DEFAULT(f); +</pre><p> + </p></li><li class="listitem"><p> + Create a new script based on <code class="filename">reformat_dat_file.pl</code> + to insert appropriate values on-the-fly: +</p><pre class="programlisting"> +- # At this point we have the full row in memory as a hash +- # and can do any operations we want. As written, it only +- # removes default values, but this script can be adapted to +- # do one-off bulk-editing. ++ # One-off change to migrate to prokind ++ # Default has already been filled in by now, so change to other ++ # values as appropriate ++ if ($values{proisagg} eq 't') ++ { ++ $values{prokind} = 'a'; ++ } ++ elsif ($values{proiswindow} eq 't') ++ { ++ $values{prokind} = 'w'; ++ } +</pre><p> + </p></li><li class="listitem"><p> + Run the new script: +</p><pre class="programlisting"> +$ cd src/include/catalog +$ perl rewrite_dat_with_prokind.pl pg_proc.dat +</pre><p> + At this point <code class="filename">pg_proc.dat</code> has all three + columns, <code class="structfield">prokind</code>, + <code class="structfield">proisagg</code>, + and <code class="structfield">proiswindow</code>, though they will appear + only in rows where they have non-default values. + </p></li><li class="listitem"><p> + Remove the old columns from <code class="filename">pg_proc.h</code>: +</p><pre class="programlisting"> +- /* is it an aggregate? */ +- bool proisagg BKI_DEFAULT(f); +- +- /* is it a window function? */ +- bool proiswindow BKI_DEFAULT(f); +</pre><p> + </p></li><li class="listitem"><p> + Finally, run <code class="literal">make reformat-dat-files</code> to remove + the useless old entries from <code class="filename">pg_proc.dat</code>. + </p></li></ol></div><p> + + For further examples of scripts used for bulk editing, see + <code class="filename">convert_oid2name.pl</code> + and <code class="filename">remove_pg_type_oid_symbols.pl</code> attached to this + message: + <a class="ulink" href="https://www.postgresql.org/message-id/CAJVSVGVX8gXnPm+Xa=DxR7kFYprcQ1tNcCT5D0O3ShfnM6jehA@mail.gmail.com" target="_top">https://www.postgresql.org/message-id/CAJVSVGVX8gXnPm+Xa=DxR7kFYprcQ1tNcCT5D0O3ShfnM6jehA@mail.gmail.com</a> + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="system-catalog-declarations.html" title="70.1. System Catalog Declaration Rules">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="bki.html" title="Chapter 70. System Catalog Declarations and Initial Contents">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="bki-format.html" title="70.3. BKI File Format">Next</a></td></tr><tr><td width="40%" align="left" valign="top">70.1. System Catalog Declaration Rules </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 70.3. <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">BKI</acronym> File Format</td></tr></table></div></body></html>
\ No newline at end of file |