Chapter 15. Interfacing Extensions To Indexes
In the Red Hat Database SQL Guide and Reference we described how to create a new type, new functions that operate on those types, and new operators. However, you could not yet define a secondary index (such as a B-tree, R-tree or hash access method) over a new type or its operators. This chapter describes how this could be accomplished. You will have to manually update the system catalogs, so we will begin with a brief review of the catalog structure. Please refer to Chapter 20 for more information about the Red Hat Database Catalogs.
About the Red Hat Database System Catalogs
First take a look at how the catalogs are laid out. You can skip this section for now, but some later sections may be incomprehensible without the information given here, so mark this page for later reference. All system catalogs have names that begin with pg_. The following tables contain information that may be useful to the end user. (There are many other system catalogs, but there should rarely be a reason to query them directly.)
Table 15-1. Major PostgreSQL System Catalogs
| Catalog Name | Description |
|---|---|
| pg_database | databases |
| pg_class | tables |
| pg_attribute | table columns |
| pg_index | secondary indexes |
| pg_proc | procedures (both C and SQL) |
| pg_type | types (both base and complex) |
| pg_operator | operators |
| pg_aggregate | aggregates and aggregate functions |
| pg_am | access methods |
| pg_amop | access method operators |
| pg_amproc | access method support functions |
| pg_opclass | access method operator classes |
In several of the sections that follow, we will present various join queries on the system catalogs that display information we need to extend the system. Looking at this diagram should make some of these join queries (which are often three- or four-way joins) more understandable, because you will be able to see that the columns used in the queries form foreign keys in other tables.
Many different features (tables, columns, functions, types, access methods, and so on) are tightly integrated in this schema. A simple create command may modify many of these catalogs.
Types and procedures are central to the schema. Nearly every catalog contains some reference to rows in one or both of these tables. For example, PostgreSQL frequently uses type signatures (for example, of functions and operators) to identify unique rows of other catalogs.
There are many columns and relationships that have obvious meanings, but there are many (particularly those that have to do with access methods) that do not. The relationships between pg_am, pg_amop, pg_amproc, pg_operator, and pg_opclass are particularly hard to understand and will be described in depth in the Section called Modifying the Catalogs to Extend Indexes.
 | We use the words procedure and function more or less interchangeably throughout this chapter. |