Modifying the Catalogs to Extend Indexes

Look back at Figure 15-1. The right half shows the catalogs that we must modify in order to tell PostgreSQL how to use a user-defined type and/or user-defined operators with an index (that is, pg_am, pg_amop, pg_amproc, pg_operator and pg_opclass). Unfortunately, there is no simple command to do this. We will demonstrate how to modify these catalogs through a running example: a new operator class for the B-tree access method that stores and sorts complex numbers in ascending absolute value order.

The pg_am table contains one row for every user defined access method. Support for the heap access method is built into PostgreSQL, but every other access method is described here. The schema is shown in the following table:

Table 15-2. pg_am Schema

ColumnDescription
amnameName of the access method
amownerUser id of the owner
amstrategiesNumber of strategies for this access method (see below)
amsupportNumber of support routines for this access method (see below)
amorderstrategyZero if the index offers no sort order, otherwise the strategy number of the strategy operator that describes the sort order
amgettuple"Next valid tuple" function
aminsert"Insert valid tuple" function
...Procedure identifiers for interface routines to the access method. For example, regproc ids for opening, closing, and getting rows from the access method appear here.

The object ID of the row in pg_am is used as a foreign key in many other tables. You do not need to add a new row to this table; all that you need is the object ID of the row of the access method you want to extend:
SELECT oid FROM pg_am WHERE amname = 'btree';

 oid
-----
 403
(1 row)
We will use this query in a WHERE clause later.

The amstrategies column exists to standardize comparisons across data types. For example, B-trees impose a strict ordering on keys, lesser to greater. PostgreSQL allows the user to define operators, but it cannot tell by looking at the name of an operator (for example, ">" or "<") what kind of comparison it is. In fact, some access methods do not impose any ordering at all. For example, R-trees express a rectangle-containment relationship, whereas a hashed data structure expresses only bitwise similarity based on the value of a hash function. PostgreSQL needs some consistent way of taking a qualification in your query, looking at the operator and then deciding if a usable index exists. This implies that Red Hat Database needs to know, for example, that the "<=" and ">" operators partition a B-tree. Red Hat Database uses strategies to express these relationships between operators and the way they can be used to scan indexes.

Defining a new set of strategies is beyond the scope of this discussion, but we will explain how B-tree strategies work because you will need to know how that is done in order to add a new operator class. In the pg_am table, the amstrategies column is the number of strategies defined for this access method. For B-trees, this number is 5. These strategies are shown in the following table:

Table 15-3. B-tree Strategies

OperationIndex
less than1
less than or equal2
equal3
greater than or equal4
greater than5

The idea is that you will need to add procedures corresponding to the comparisons above to the pg_amop relation (see below). The access method code can use these strategy numbers, regardless of data type, to figure out how to partition the B-tree, compute selectivity, and so on. Do not worry about the details of adding procedures yet; just understand that there must be a set of these procedures for int2, int4, oid, and every other data type on which a B-tree can operate.