<?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>69.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 Vsnapshot" /><link rel="prev" href="system-catalog-declarations.html" title="69.1. System Catalog Declaration Rules" /><link rel="next" href="bki-format.html" title="69.3. BKI File Format" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">69.2. System Catalog Initial Data</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="system-catalog-declarations.html" title="69.1. System Catalog Declaration Rules">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="bki.html" title="Chapter 69. System Catalog Declarations and Initial Contents">Up</a></td><th width="60%" align="center">Chapter 69. System Catalog Declarations and Initial Contents</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="bki-format.html" title="69.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">69.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">69.2.1. Data File Format</a></span></dt><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-OID-ASSIGNMENT">69.2.2. OID Assignment</a></span></dt><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-OID-REFERENCES">69.2.3. OID Reference Lookup</a></span></dt><dt><span class="sect2"><a href="system-catalog-initial-data.html#SYSTEM-CATALOG-RECIPES">69.2.4. 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">69.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', datdba => 'PGUID', encoding => 'ENCODING',
datcollate => 'LC_COLLATE', datctype => 'LC_CTYPE', datistemplate => 't',
datallowconn => 't', datconnlimit => '-1', datlastsysoid => '0',
datfrozenxid => '0', datminmxid => '1', dattablespace => '1663',
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>, 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="69.2.2. OID Assignment">Section 69.2.2</a>
below. <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.
</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>
To aid readability, field values that are OIDs of other catalog
entries can be represented by names rather than numeric OIDs.
This is described in <a class="xref" href="system-catalog-initial-data.html#SYSTEM-CATALOG-OID-REFERENCES" title="69.2.3. OID Reference Lookup">Section 69.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>,
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">69.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, or leaves it zero in a catalog that has no OIDs.
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 <span class="quote">“<span class="quote">45-900</span>”</span> 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 also detect duplicate OIDs
at compile time.)
</p><p>
The OID counter starts at 10000 at the beginning of a bootstrap run.
If a catalog row is in a table that requires OIDs, but no OID was
preassigned by an <code class="literal">oid</code> field, then it will
receive an OID of 10000 or above.
</p></div><div class="sect2" id="SYSTEM-CATALOG-OID-REFERENCES"><div class="titlepage"><div><div><h3 class="title">69.2.3. OID Reference Lookup</h3></div></div></div><p>
Cross-references from one initial catalog row to another can be written
by just writing the preassigned OID of the referenced row. But
that's error-prone and hard to understand, so for frequently-referenced
catalogs, <code class="filename">genbki.pl</code> provides mechanisms to write
symbolic references instead. Currently this is possible for references
to access methods, functions, operators, opclasses, opfamilies, and
types. 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 <code class="literal">pg_am</code>, <code class="literal">pg_proc</code>,
<code class="literal">pg_operator</code>, <code class="literal">pg_opclass</code>,
<code class="literal">pg_opfamily</code>, or <code class="literal">pg_type</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>
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>
Access methods are just represented by their names, as are types.
Type names must 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 pg_catalog schema.
</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-RECIPES"><div class="titlepage"><div><div><h3 class="title">69.2.4. 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 class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="system-catalog-declarations.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="bki.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="bki-format.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">69.1. System Catalog Declaration Rules </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 69.3. <acronym class="acronym">BKI</acronym> File Format</td></tr></table></div></body></html>
