1 <!-- doc/src/sgml/bki.sgml -->
4 <title>System Catalog Declarations and Initial Contents</title>
7 <productname>PostgreSQL</productname> uses many different system catalogs
8 to keep track of the existence and properties of database objects, such as
9 tables and functions. Physically there is no difference between a system
10 catalog and a plain user table, but the backend C code knows the structure
11 and properties of each catalog, and can manipulate it directly at a low
12 level. Thus, for example, it is inadvisable to attempt to alter the
13 structure of a catalog on-the-fly; that would break assumptions built into
14 the C code about how rows of the catalog are laid out. But the structure
15 of the catalogs can change between major versions.
19 The structures of the catalogs are declared in specially formatted C
20 header files in the <filename>src/include/catalog/</filename> directory of
21 the source tree. In particular, for each catalog there is a header file
22 named after the catalog (e.g., <filename>pg_class.h</filename>
23 for <structname>pg_class</structname>), which defines the set of columns
24 the catalog has, as well as some other basic properties such as its OID.
25 Other critical files defining the catalog structure
26 include <filename>indexing.h</filename>, which defines the indexes present
27 on all the system catalogs, and <filename>toasting.h</filename>, which
28 defines TOAST tables for catalogs that need one.
32 Many of the catalogs have initial data that must be loaded into them
33 during the <quote>bootstrap</quote> phase
34 of <application>initdb</application>, to bring the system up to a point
35 where it is capable of executing SQL commands. (For
36 example, <filename>pg_class.h</filename> must contain an entry for itself,
37 as well as one for each other system catalog and index.) This
38 initial data is kept in editable form in data files that are also stored
39 in the <filename>src/include/catalog/</filename> directory. For example,
40 <filename>pg_proc.dat</filename> describes all the initial rows that must
41 be inserted into the <structname>pg_proc</structname> catalog.
45 To create the catalog files and load this initial data into them, a
46 backend running in bootstrap mode reads a <acronym>BKI</acronym>
47 (Backend Interface) file containing commands and initial data.
48 The <filename>postgres.bki</filename> file used in this mode is prepared
49 from the aforementioned header and data files, while building
50 a <productname>PostgreSQL</productname> distribution, by a Perl script
51 named <filename>genbki.pl</filename>.
52 Although it's specific to a particular <productname>PostgreSQL</productname>
53 release, <filename>postgres.bki</filename> is platform-independent and is
54 installed in the <filename>share</filename> subdirectory of the
59 <filename>genbki.pl</filename> also produces a derived header file for
60 each catalog, for example <filename>pg_class_d.h</filename> for
61 the <structname>pg_class</structname> catalog. This file contains
62 automatically-generated macro definitions, and may contain other macros,
63 enum declarations, and so on that can be useful for client C code that
64 reads a particular catalog.
68 Most Postgres developers don't need to be directly concerned with
69 the <acronym>BKI</acronym> file, but almost any nontrivial feature
70 addition in the backend will require modifying the catalog header files
71 and/or initial data files. The rest of this chapter gives some
72 information about that, and for completeness describes
73 the <acronym>BKI</acronym> file format.
76 <sect1 id="system-catalog-declarations">
77 <title>System Catalog Declaration Rules</title>
80 The key part of a catalog header file is a C structure definition
81 describing the layout of each row of the catalog. This begins with
82 a <literal>CATALOG</literal> macro, which so far as the C compiler is
83 concerned is just shorthand for <literal>typedef struct
84 FormData_<replaceable>catalogname</replaceable></literal>.
85 Each field in the struct gives rise to a catalog column.
86 Fields can be annotated using the BKI property macros described
87 in <filename>genbki.h</filename>, for example to define a default value
88 for a field or mark it as nullable or not nullable.
89 The <literal>CATALOG</literal> line can also be annotated, with some
90 other BKI property macros described in <filename>genbki.h</filename>, to
91 define other properties of the catalog as a whole, such as whether
92 it has OIDs (by default, it does).
96 The system catalog cache code (and most catalog-munging code in general)
97 assumes that the fixed-length portions of all system catalog tuples are
98 in fact present, because it maps this C struct declaration onto them.
99 Thus, all variable-length fields and nullable fields must be placed at
100 the end, and they cannot be accessed as struct fields.
101 For example, if you tried to
102 set <structname>pg_type</structname>.<structfield>typrelid</structfield>
103 to be NULL, it would fail when some piece of code tried to reference
104 <literal>typetup->typrelid</literal> (or worse,
105 <literal>typetup->typelem</literal>, because that follows
106 <structfield>typrelid</structfield>). This would result in
107 random errors or even segmentation violations.
111 As a partial guard against this type of error, variable-length or
112 nullable fields should not be made directly visible to the C compiler.
113 This is accomplished by wrapping them in <literal>#ifdef
114 CATALOG_VARLEN</literal> ... <literal>#endif</literal> (where
115 <literal>CATALOG_VARLEN</literal> is a symbol that is never defined).
116 This prevents C code from carelessly trying to access fields that might
117 not be there or might be at some other offset.
118 As an independent guard against creating incorrect rows, we
119 require all columns that should be non-nullable to be marked so
120 in <structname>pg_attribute</structname>. The bootstrap code will
121 automatically mark catalog columns as <literal>NOT NULL</literal>
122 if they are fixed-width and are not preceded by any nullable column.
123 Where this rule is inadequate, you can force correct marking by using
124 <literal>BKI_FORCE_NOT_NULL</literal>
125 and <literal>BKI_FORCE_NULL</literal> annotations as needed. But note
126 that <literal>NOT NULL</literal> constraints are only enforced in the
127 executor, not against tuples that are generated by random C code,
128 so care is still needed when manually creating or updating catalog rows.
132 Frontend code should not include any <structname>pg_xxx.h</structname>
133 catalog header file, as these files may contain C code that won't compile
134 outside the backend. (Typically, that happens because these files also
135 contain declarations for functions
136 in <filename>src/backend/catalog/</filename> files.)
137 Instead, frontend code may include the corresponding
138 generated <structname>pg_xxx_d.h</structname> header, which will contain
139 OID <literal>#define</literal>s and any other data that might be of use
140 on the client side. If you want macros or other code in a catalog header
141 to be visible to frontend code, write <literal>#ifdef
142 EXPOSE_TO_CLIENT_CODE</literal> ... <literal>#endif</literal> around that
143 section to instruct <filename>genbki.pl</filename> to copy that section
144 to the <structname>pg_xxx_d.h</structname> header.
148 A few of the catalogs are so fundamental that they can't even be created
149 by the <acronym>BKI</acronym> <literal>create</literal> command that's
150 used for most catalogs, because that command needs to write information
151 into these catalogs to describe the new catalog. These are
152 called <firstterm>bootstrap</firstterm> catalogs, and defining one takes
153 a lot of extra work: you have to manually prepare appropriate entries for
154 them in the pre-loaded contents of <structname>pg_class</structname>
155 and <structname>pg_type</structname>, and those entries will need to be
156 updated for subsequent changes to the catalog's structure.
157 (Bootstrap catalogs also need pre-loaded entries
158 in <structname>pg_attribute</structname>, but
159 fortunately <filename>genbki.pl</filename> handles that chore nowadays.)
160 Avoid making new catalogs be bootstrap catalogs if at all possible.
164 <sect1 id="system-catalog-initial-data">
165 <title>System Catalog Initial Data</title>
168 Each catalog that has any manually-created initial data (some do not)
169 has a corresponding <literal>.dat</literal> file that contains its
170 initial data in an editable format.
173 <sect2 id="system-catalog-initial-data-format">
174 <title>Data File Format</title>
177 Each <literal>.dat</literal> file contains Perl data structure literals
178 that are simply eval'd to produce an in-memory data structure consisting
179 of an array of hash references, one per catalog row.
180 A slightly modified excerpt from <filename>pg_database.dat</filename>
181 will demonstrate the key features:
187 # LC_COLLATE and LC_CTYPE will be replaced at initdb time with user choices
188 # that might contain non-word characters, so we must double-quote them.
190 { oid => '1', oid_symbol => 'TemplateDbOid',
191 descr => 'database\'s default template',
192 datname => 'template1', datdba => 'PGUID', encoding => 'ENCODING',
193 datcollate => '"LC_COLLATE"', datctype => '"LC_CTYPE"', datistemplate => 't',
194 datallowconn => 't', datconnlimit => '-1', datlastsysoid => '0',
195 datfrozenxid => '0', datminmxid => '1', dattablespace => '1663',
196 datacl => '_null_' },
209 The overall file layout is: open square bracket, one or more sets of
210 curly braces each of which represents a catalog row, close square
211 bracket. Write a comma after each closing curly brace.
217 Within each catalog row, write comma-separated
218 <replaceable>key</replaceable> <literal>=></literal>
219 <replaceable>value</replaceable> pairs. The
220 allowed <replaceable>key</replaceable>s are the names of the catalog's
221 columns, plus the metadata keys <literal>oid</literal>,
222 <literal>oid_symbol</literal>, and <literal>descr</literal>.
223 (The use of <literal>oid</literal> and <literal>oid_symbol</literal>
224 is described in <xref linkend="system-catalog-oid-assignment"/>
225 below. <literal>descr</literal> supplies a description string for
226 the object, which will be inserted
227 into <structname>pg_description</structname>
228 or <structname>pg_shdescription</structname> as appropriate.)
229 While the metadata keys are optional, the catalog's defined columns
230 must all be provided, except when the catalog's <literal>.h</literal>
231 file specifies a default value for the column.
237 All values must be single-quoted. Escape single quotes used within
238 a value with a backslash. (Backslashes meant as data need not be
239 doubled, however; this follows Perl's rules for simple quoted
246 Null values are represented by <literal>_null_</literal>.
252 If a value is a macro to be expanded
253 by <application>initdb</application>, it should also contain double
254 quotes as shown above, unless we know that no special characters can
255 appear within the string that will be substituted.
261 Comments are preceded by <literal>#</literal>, and must be on their
268 To aid readability, field values that are OIDs of other catalog
269 entries can be represented by names rather than numeric OIDs.
270 This is described in <xref linkend="system-catalog-oid-references"/>
277 Since hashes are unordered data structures, field order and line
278 layout aren't semantically significant. However, to maintain a
279 consistent appearance, we set a few rules that are applied by the
280 formatting script <filename>reformat_dat_file.pl</filename>:
286 Within each pair of curly braces, the metadata
287 fields <literal>oid</literal>, <literal>oid_symbol</literal>,
288 and <literal>descr</literal> (if present) come first, in that
289 order, then the catalog's own fields appear in their defined order.
295 Newlines are inserted between fields as needed to limit line length
296 to 80 characters, if possible. A newline is also inserted between
297 the metadata fields and the regular fields.
303 If the catalog's <literal>.h</literal> file specifies a default
304 value for a column, and a data entry has that same
305 value, <filename>reformat_dat_file.pl</filename> will omit it from
306 the data file. This keeps the data representation compact.
312 <filename>reformat_dat_file.pl</filename> preserves blank lines
313 and comment lines as-is.
319 It's recommended to run <filename>reformat_dat_file.pl</filename>
320 before submitting catalog data patches. For convenience, you can
321 simply change to <filename>src/include/catalog/</filename> and
322 run <literal>make reformat-dat-files</literal>.
328 If you want to add a new method of making the data representation
329 smaller, you must implement it
330 in <filename>reformat_dat_file.pl</filename> and also
331 teach <function>Catalog::ParseData()</function> how to expand the
332 data back into the full representation.
339 <sect2 id="system-catalog-oid-assignment">
340 <title>OID Assignment</title>
343 A catalog row appearing in the initial data can be given a
344 manually-assigned OID by writing an <literal>oid
345 => <replaceable>nnnn</replaceable></literal> metadata field.
346 Furthermore, if an OID is assigned, a C macro for that OID can be
347 created by writing an <literal>oid_symbol
348 => <replaceable>name</replaceable></literal> metadata field.
352 Pre-loaded catalog rows must have preassigned OIDs if there are OID
353 references to them in other pre-loaded rows. A preassigned OID is
354 also needed if the row's OID must be referenced from C code.
355 If neither case applies, the <literal>oid</literal> metadata field can
356 be omitted, in which case the bootstrap code assigns an OID
357 automatically, or leaves it zero in a catalog that has no OIDs.
358 In practice we usually preassign OIDs for all or none of the pre-loaded
359 rows in a given catalog, even if only some of them are actually
364 Writing the actual numeric value of any OID in C code is considered
365 very bad form; always use a macro, instead. Direct references
366 to <structname>pg_proc</structname> OIDs are common enough that there's
367 a special mechanism to create the necessary macros automatically;
368 see <filename>src/backend/utils/Gen_fmgrtab.pl</filename>. Similarly
369 — but, for historical reasons, not done the same way —
370 there's an automatic method for creating macros
371 for <structname>pg_type</structname>
372 OIDs. <literal>oid_symbol</literal> entries are therefore not
373 necessary in those two catalogs. Likewise, macros for
374 the <structname>pg_class</structname> OIDs of system catalogs and
375 indexes are set up automatically. For all other system catalogs, you
376 have to manually specify any macros you need
377 via <literal>oid_symbol</literal> entries.
381 To find an available OID for a new pre-loaded row, run the
382 script <filename>src/include/catalog/unused_oids</filename>.
383 It prints inclusive ranges of unused OIDs (e.g., the output
384 line <quote>45-900</quote> means OIDs 45 through 900 have not been
385 allocated yet). Currently, OIDs 1-9999 are reserved for manual
386 assignment; the <filename>unused_oids</filename> script simply looks
387 through the catalog headers and <filename>.dat</filename> files
388 to see which ones do not appear. You can also use
389 the <filename>duplicate_oids</filename> script to check for mistakes.
390 (That script is run automatically at compile time, and will stop the
391 build if a duplicate is found.)
395 The OID counter starts at 10000 at the beginning of a bootstrap run.
396 If a catalog row is in a table that requires OIDs, but no OID was
397 preassigned by an <literal>oid</literal> field, then it will
398 receive an OID of 10000 or above.
402 <sect2 id="system-catalog-oid-references">
403 <title>OID Reference Lookup</title>
406 Cross-references from one initial catalog row to another can be written
407 by just writing the preassigned OID of the referenced row. But
408 that's error-prone and hard to understand, so for frequently-referenced
409 catalogs, <filename>genbki.pl</filename> provides mechanisms to write
410 symbolic references instead. Currently this is possible for references
411 to access methods, functions, operators, opclasses, opfamilies, and
412 types. The rules are as follows:
419 Use of symbolic references is enabled in a particular catalog column
420 by attaching <literal>BKI_LOOKUP(<replaceable>lookuprule</replaceable>)</literal>
421 to the column's definition, where <replaceable>lookuprule</replaceable>
422 is <structname>pg_am</structname>, <structname>pg_proc</structname>,
423 <structname>pg_operator</structname>,
424 <structname>pg_opclass</structname>,
425 <structname>pg_opfamily</structname>,
426 or <structname>pg_type</structname>.
427 <literal>BKI_LOOKUP</literal> can be attached to columns of
428 type <type>Oid</type>, <type>regproc</type>, <type>oidvector</type>,
429 or <type>Oid[]</type>; in the latter two cases it implies performing a
430 lookup on each element of the array.
436 In such a column, all entries must use the symbolic format except
437 when writing <literal>0</literal> for InvalidOid. (If the column is
438 declared <type>regproc</type>, you can optionally
439 write <literal>-</literal> instead of <literal>0</literal>.)
440 <filename>genbki.pl</filename> will warn about unrecognized names.
446 Access methods are just represented by their names, as are types.
447 Type names must match the referenced <structname>pg_type</structname>
448 entry's <structfield>typname</structfield>; you do not get to use any
449 aliases such as <literal>integer</literal>
450 for <literal>int4</literal>.
456 A function can be represented by
457 its <structfield>proname</structfield>, if that is unique among
458 the <filename>pg_proc.dat</filename> entries (this works like regproc
459 input). Otherwise, write it
460 as <replaceable>proname(argtypename,argtypename,...)</replaceable>,
461 like regprocedure. The argument type names must be spelled exactly as
462 they are in the <filename>pg_proc.dat</filename> entry's
463 <structfield>proargtypes</structfield> field. Do not insert any
470 Operators are represented
471 by <replaceable>oprname(lefttype,righttype)</replaceable>,
472 writing the type names exactly as they appear in
473 the <filename>pg_operator.dat</filename>
474 entry's <structfield>oprleft</structfield>
475 and <structfield>oprright</structfield> fields.
476 (Write <literal>0</literal> for the omitted operand of a unary
483 The names of opclasses and opfamilies are only unique within an
484 access method, so they are represented
485 by <replaceable>access_method_name</replaceable><literal>/</literal><replaceable>object_name</replaceable>.
491 In none of these cases is there any provision for
492 schema-qualification; all objects created during bootstrap are
493 expected to be in the pg_catalog schema.
499 <filename>genbki.pl</filename> resolves all symbolic references while it
500 runs, and puts simple numeric OIDs into the emitted BKI file. There is
501 therefore no need for the bootstrap backend to deal with symbolic
506 <sect2 id="system-catalog-recipes">
507 <title>Recipes for Editing Data Files</title>
510 Here are some suggestions about the easiest ways to perform common tasks
511 when updating catalog data files.
515 <title>Add a new column with a default to a catalog:</title>
517 Add the column to the header file with
518 a <literal>BKI_DEFAULT(<replaceable>value</replaceable>)</literal>
519 annotation. The data file need only be adjusted by adding the field
520 in existing rows where a non-default value is needed.
525 <title>Add a default value to an existing column that doesn't have
528 Add a <literal>BKI_DEFAULT</literal> annotation to the header file,
529 then run <literal>make reformat-dat-files</literal> to remove
530 now-redundant field entries.
535 <title>Remove a column, whether it has a default or not:</title>
537 Remove the column from the header, then run <literal>make
538 reformat-dat-files</literal> to remove now-useless field entries.
543 <title>Change or remove an existing default value:</title>
545 You cannot simply change the header file, since that will cause the
546 current data to be interpreted incorrectly. First run <literal>make
547 expand-dat-files</literal> to rewrite the data files with all
548 default values inserted explicitly, then change or remove
549 the <literal>BKI_DEFAULT</literal> annotation, then run <literal>make
550 reformat-dat-files</literal> to remove superfluous fields again.
555 <title>Ad-hoc bulk editing:</title>
557 <filename>reformat_dat_file.pl</filename> can be adapted to perform
558 many kinds of bulk changes. Look for its block comments showing where
559 one-off code can be inserted. In the following example, we are going
560 to consolidate two boolean fields in <structname>pg_proc</structname>
566 Add the new column, with a default,
567 to <filename>pg_proc.h</filename>:
569 + /* see PROKIND_ categories below */
570 + char prokind BKI_DEFAULT(f);
577 Create a new script based on <filename>reformat_dat_file.pl</filename>
578 to insert appropriate values on-the-fly:
580 - # At this point we have the full row in memory as a hash
581 - # and can do any operations we want. As written, it only
582 - # removes default values, but this script can be adapted to
583 - # do one-off bulk-editing.
584 + # One-off change to migrate to prokind
585 + # Default has already been filled in by now, so change to other
586 + # values as appropriate
587 + if ($values{proisagg} eq 't')
589 + $values{prokind} = 'a';
591 + elsif ($values{proiswindow} eq 't')
593 + $values{prokind} = 'w';
603 $ cd src/include/catalog
604 $ perl -I ../../backend/catalog rewrite_dat_with_prokind.pl pg_proc.dat
606 At this point <filename>pg_proc.dat</filename> has all three
607 columns, <structfield>prokind</structfield>,
608 <structfield>proisagg</structfield>,
609 and <structfield>proiswindow</structfield>, though they will appear
610 only in rows where they have non-default values.
616 Remove the old columns from <filename>pg_proc.h</filename>:
618 - /* is it an aggregate? */
619 - bool proisagg BKI_DEFAULT(f);
621 - /* is it a window function? */
622 - bool proiswindow BKI_DEFAULT(f);
629 Finally, run <literal>make reformat-dat-files</literal> to remove
630 the useless old entries from <filename>pg_proc.dat</filename>.
635 For further examples of scripts used for bulk editing, see
636 <filename>convert_oid2name.pl</filename>
637 and <filename>remove_pg_type_oid_symbols.pl</filename> attached to this
639 <ulink url="https://www.postgresql.org/message-id/CAJVSVGVX8gXnPm+Xa=DxR7kFYprcQ1tNcCT5D0O3ShfnM6jehA@mail.gmail.com"></ulink>
645 <sect1 id="bki-format">
646 <title><acronym>BKI</acronym> File Format</title>
649 This section describes how the <productname>PostgreSQL</productname>
650 backend interprets <acronym>BKI</acronym> files. This description
651 will be easier to understand if the <filename>postgres.bki</filename>
652 file is at hand as an example.
656 <acronym>BKI</acronym> input consists of a sequence of commands. Commands are made up
657 of a number of tokens, depending on the syntax of the command.
658 Tokens are usually separated by whitespace, but need not be if
659 there is no ambiguity. There is no special command separator; the
660 next token that syntactically cannot belong to the preceding
661 command starts a new one. (Usually you would put a new command on
662 a new line, for clarity.) Tokens can be certain key words, special
663 characters (parentheses, commas, etc.), numbers, or double-quoted
664 strings. Everything is case sensitive.
668 Lines starting with <literal>#</literal> are ignored.
673 <sect1 id="bki-commands">
674 <title><acronym>BKI</acronym> Commands</title>
679 <literal>create</literal>
680 <replaceable class="parameter">tablename</replaceable>
681 <replaceable class="parameter">tableoid</replaceable>
682 <optional><literal>bootstrap</literal></optional>
683 <optional><literal>shared_relation</literal></optional>
684 <optional><literal>without_oids</literal></optional>
685 <optional><literal>rowtype_oid</literal> <replaceable>oid</replaceable></optional>
686 (<replaceable class="parameter">name1</replaceable> =
687 <replaceable class="parameter">type1</replaceable>
688 <optional><literal>FORCE NOT NULL</literal> | <literal>FORCE NULL</literal> </optional> <optional>,
689 <replaceable class="parameter">name2</replaceable> =
690 <replaceable class="parameter">type2</replaceable>
691 <optional><literal>FORCE NOT NULL</literal> | <literal>FORCE NULL</literal> </optional>,
697 Create a table named <replaceable
698 class="parameter">tablename</replaceable>, and having the OID
699 <replaceable class="parameter">tableoid</replaceable>,
700 with the columns given in parentheses.
704 The following column types are supported directly by
705 <filename>bootstrap.c</filename>: <type>bool</type>,
706 <type>bytea</type>, <type>char</type> (1 byte),
707 <type>name</type>, <type>int2</type>,
708 <type>int4</type>, <type>regproc</type>, <type>regclass</type>,
709 <type>regtype</type>, <type>text</type>,
710 <type>oid</type>, <type>tid</type>, <type>xid</type>,
711 <type>cid</type>, <type>int2vector</type>, <type>oidvector</type>,
712 <type>_int4</type> (array), <type>_text</type> (array),
713 <type>_oid</type> (array), <type>_char</type> (array),
714 <type>_aclitem</type> (array). Although it is possible to create
715 tables containing columns of other types, this cannot be done until
716 after <structname>pg_type</structname> has been created and filled with
717 appropriate entries. (That effectively means that only these
718 column types can be used in bootstrap catalogs, but non-bootstrap
719 catalogs can contain any built-in type.)
723 When <literal>bootstrap</literal> is specified,
724 the table will only be created on disk; nothing is entered into
725 <structname>pg_class</structname>,
726 <structname>pg_attribute</structname>, etc, for it. Thus the
727 table will not be accessible by ordinary SQL operations until
728 such entries are made the hard way (with <literal>insert</literal>
729 commands). This option is used for creating
730 <structname>pg_class</structname> etc themselves.
734 The table is created as shared if <literal>shared_relation</literal> is
736 It will have OIDs unless <literal>without_oids</literal> is specified.
737 The table's row type OID (<structname>pg_type</structname> OID) can optionally
738 be specified via the <literal>rowtype_oid</literal> clause; if not specified,
739 an OID is automatically generated for it. (The <literal>rowtype_oid</literal>
740 clause is useless if <literal>bootstrap</literal> is specified, but it can be
741 provided anyway for documentation.)
748 <literal>open</literal> <replaceable class="parameter">tablename</replaceable>
754 <replaceable class="parameter">tablename</replaceable>
755 for insertion of data. Any currently open table is closed.
762 <literal>close</literal> <optional><replaceable class="parameter">tablename</replaceable></optional>
767 Close the open table. The name of the table can be given as a
768 cross-check, but this is not required.
775 <literal>insert</literal> <optional><literal>OID =</literal> <replaceable class="parameter">oid_value</replaceable></optional> <literal>(</literal> <replaceable class="parameter">value1</replaceable> <replaceable class="parameter">value2</replaceable> ... <literal>)</literal>
780 Insert a new row into the open table using <replaceable
781 class="parameter">value1</replaceable>, <replaceable
782 class="parameter">value2</replaceable>, etc., for its column
783 values and <replaceable
784 class="parameter">oid_value</replaceable> for its OID. If
785 <replaceable class="parameter">oid_value</replaceable> is zero
786 (0) or the clause is omitted, and the table has OIDs, then the
787 next available OID is assigned.
791 NULL values can be specified using the special key word
792 <literal>_null_</literal>. Values containing spaces must be
800 <literal>declare</literal> <optional><literal>unique</literal></optional>
801 <literal>index</literal> <replaceable class="parameter">indexname</replaceable>
802 <replaceable class="parameter">indexoid</replaceable>
803 <literal>on</literal> <replaceable class="parameter">tablename</replaceable>
804 <literal>using</literal> <replaceable class="parameter">amname</replaceable>
805 <literal>(</literal> <replaceable class="parameter">opclass1</replaceable>
806 <replaceable class="parameter">name1</replaceable>
807 <optional>, ...</optional> <literal>)</literal>
812 Create an index named <replaceable
813 class="parameter">indexname</replaceable>, having OID
814 <replaceable class="parameter">indexoid</replaceable>,
816 <replaceable class="parameter">tablename</replaceable>, using the
817 <replaceable class="parameter">amname</replaceable> access
818 method. The fields to index are called <replaceable
819 class="parameter">name1</replaceable>, <replaceable
820 class="parameter">name2</replaceable> etc., and the operator
821 classes to use are <replaceable
822 class="parameter">opclass1</replaceable>, <replaceable
823 class="parameter">opclass2</replaceable> etc., respectively.
824 The index file is created and appropriate catalog entries are
825 made for it, but the index contents are not initialized by this command.
832 <literal>declare toast</literal>
833 <replaceable class="parameter">toasttableoid</replaceable>
834 <replaceable class="parameter">toastindexoid</replaceable>
835 <literal>on</literal> <replaceable class="parameter">tablename</replaceable>
840 Create a TOAST table for the table named
841 <replaceable class="parameter">tablename</replaceable>.
842 The TOAST table is assigned OID
843 <replaceable class="parameter">toasttableoid</replaceable>
844 and its index is assigned OID
845 <replaceable class="parameter">toastindexoid</replaceable>.
846 As with <literal>declare index</literal>, filling of the index
853 <term><literal>build indices</literal></term>
857 Fill in the indices that have previously been declared.
865 <sect1 id="bki-structure">
866 <title>Structure of the Bootstrap <acronym>BKI</acronym> File</title>
869 The <literal>open</literal> command cannot be used until the tables it uses
870 exist and have entries for the table that is to be opened.
871 (These minimum tables are <structname>pg_class</structname>,
872 <structname>pg_attribute</structname>, <structname>pg_proc</structname>, and
873 <structname>pg_type</structname>.) To allow those tables themselves to be filled,
874 <literal>create</literal> with the <literal>bootstrap</literal> option implicitly opens
875 the created table for data insertion.
879 Also, the <literal>declare index</literal> and <literal>declare toast</literal>
880 commands cannot be used until the system catalogs they need have been
881 created and filled in.
885 Thus, the structure of the <filename>postgres.bki</filename> file has to
890 <literal>create bootstrap</literal> one of the critical tables
895 <literal>insert</literal> data describing at least the critical tables
900 <literal>close</literal>
905 Repeat for the other critical tables.
910 <literal>create</literal> (without <literal>bootstrap</literal>) a noncritical table
915 <literal>open</literal>
920 <literal>insert</literal> desired data
925 <literal>close</literal>
930 Repeat for the other noncritical tables.
935 Define indexes and toast tables.
940 <literal>build indices</literal>
947 There are doubtless other, undocumented ordering dependencies.
951 <sect1 id="bki-example">
952 <title>BKI Example</title>
955 The following sequence of commands will create the
956 table <literal>test_table</literal> with OID 420, having two columns
957 <literal>cola</literal> and <literal>colb</literal> of type
958 <type>int4</type> and <type>text</type>, respectively, and insert
959 two rows into the table:
961 create test_table 420 (cola = int4, colb = text)
963 insert OID=421 ( 1 "value1" )
964 insert OID=422 ( 2 _null_ )