xindex.sgml

关系型数据库 Postgresql 6.5.2

 <chapter id="xindex">
  <title>Interfacing Extensions To Indices</title>

  <para>
   The procedures described thus far let you define a new type, new
   functions and new operators.  However, we cannot yet define a secondary
   index (such as a <acronym>B-tree</acronym>, <acronym>R-tree</acronym> or
   hash access method) over a new type or its operators.
  </para>

  <para>
   Look back at
   <xref endterm="EXTEND-CATALOGS" linkend="EXTEND-CATALOGS">.
   The right half shows the  catalogs  that we must modify in order to tell
   <productname>Postgres</productname> how to use a user-defined type and/or
   user-defined  operators with an index (i.e., <filename>pg_am, pg_amop,
    pg_amproc, pg_operator</filename> and <filename>pg_opclass</filename>).
   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 <acronym>B-tree</acronym> access method that stores and
   sorts complex numbers in ascending absolute value order.
  </para>

  <para>
   The <filename>pg_am</filename> class contains one instance for every user
   defined access method.  Support for the heap access method is built into
   <productname>Postgres</productname>, but every other access method is
   described here.  The schema is

   <table tocentry="1">
    <title>Index Schema</title>
    <titleabbrev>Indices</titleabbrev>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Attribute</entry>
       <entry>Description</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>amname</entry>
       <entry>name of the access method</entry>
      </row>
      <row>
       <entry>amowner</entry>
       <entry>object id of the owner's instance in pg_user</entry>
      </row>
      <row>
       <entry>amkind</entry>
       <entry>not used at present, but set to 'o' as a place holder</entry>
      </row>
      <row>
       <entry>amstrategies</entry>
       <entry>number of strategies for this access method (see below)</entry>
      </row>
      <row>
       <entry>amsupport</entry>
       <entry>number of support routines for this access method (see below)</entry>
      </row>
      <row>
       <entry>amgettuple</entry>
      </row>
      <row>
       <entry>aminsert</entry>
      </row>
      <row>
       <entry>...</entry>
       <entry>procedure  identifiers  for  interface routines to the access
	method.  For example, regproc ids for opening,  closing,  and
	getting instances from the access method appear here.</entry>
      </row>
     </tbody>
    </tgroup>
   </table>
  </para>

  <para>
   The <acronym>object ID</acronym> of the instance in
   <filename>pg_am</filename> is used as a foreign key in lots of other
   classes.  You  don't  need to  add a new instance to this class; all
   you're interested in is the <acronym>object ID</acronym> of the access
   method instance you want to extend:

   <programlisting>
SELECT oid FROM pg_am WHERE amname = 'btree';

         +----+
         |oid |
         +----+
         |403 |
         +----+
   </programlisting>

   We will use that <command>SELECT</command> in a <command>WHERE</command>
   clause later.
  </para>

  <para>
   The <filename>amstrategies</filename> attribute exists to standardize
   comparisons across data types.  For example, <acronym>B-tree</acronym>s
   impose a strict ordering on keys, lesser to greater.  Since
   <productname>Postgres</productname> allows the user to define operators,
   <productname>Postgres</productname> cannot look at the name of an operator
   (eg, ">" or "<") and tell what kind of comparison it is.  In fact,
   some  access methods don't impose any ordering at all.  For example,
   <acronym>R-tree</acronym>s express a rectangle-containment relationship,
   whereas a hashed data structure expresses only bitwise similarity based
   on the value of a hash function.  <productname>Postgres</productname>
   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 <productname>Postgres</productname> needs to know, for
   example, that the  "<="  and  ">" operators partition a
   <acronym>B-tree</acronym>.  <productname>Postgres</productname>
   uses strategies to express these relationships  between
   operators and the way they can be used to scan indices.
  </para>

  <para>
   Defining a new set of strategies is beyond the scope of this discussion,
   but we'll explain how <acronym>B-tree</acronym> strategies work because
   you'll need to know that to add a new operator class. In the
   <filename>pg_am</filename> class, the amstrategies attribute is the
   number of strategies defined for this access method. For
   <acronym>B-tree</acronym>s, this number is 5.  These strategies
   correspond to

   <table tocentry="1">
    <title>B-tree Strategies</title>
    <titleabbrev>B-tree</titleabbrev>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Operation</entry>
       <entry>Index</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>less than</entry>
       <entry>1</entry>
      </row>
      <row>
       <entry>less than or equal</entry>
       <entry>2</entry>
      </row>
      <row>
       <entry>equal</entry>
       <entry>3</entry>
      </row>
      <row>
       <entry>greater than or equal</entry>
       <entry>4</entry>
      </row>
      <row>
       <entry>greater than</entry>
       <entry>5</entry>
      </row>
     </tbody>
    </tgroup>
   </table>
  </para>

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

  <para>
   Sometimes, strategies aren't enough information for the system to figure
   out how to use an index.  Some access methods require other support
   routines in order to work. For example, the <acronym>B-tree</acronym>
   access method must be able to compare two keys and determine whether one
   is greater than, equal to, or less than the other.  Similarly, the
   <acronym>R-tree</acronym> access method must be able to compute
   intersections,  unions, and sizes of rectangles.  These
   operations do not correspond to user qualifications in
   SQL queries;  they are administrative routines used by
   the access methods, internally.
  </para>

  <para>
   In order to manage diverse support routines consistently across all
   <productname>Postgres</productname> access methods,
   <filename>pg_am</filename> includes an attribute called
   <filename>amsupport</filename>.  This attribute records the number of
   support routines used by an access method.  For <acronym>B-tree</acronym>s,
   this number is one -- the routine to take two keys and return -1, 0, or
   +1, depending on whether the first key is less than, equal
   to, or greater than the second.

   <note>
    <para>
     Strictly  speaking, this routine can return a negative
     number (< 0), 0, or a non-zero positive number (> 0).
    </para>
   </note>
  </para>

  <para>
   The <filename>amstrategies</filename> entry in pg_am is just the number
   of strategies defined for the access method in question.  The procedures
   for less than, less equal, and so on don't appear in
   <filename>pg_am</filename>.  Similarly, <filename>amsupport</filename>
   is just the number of support routines required by  the  access
   method.  The actual routines are listed elsewhere.
  </para>

  <para>
   The next class of interest is pg_opclass.  This class exists only to
   associate a name and default type with an oid.  In pg_amop, every
   <acronym>B-tree</acronym> operator class has a set of procedures, one
   through five, above. Some existing opclasses are <filename>int2_ops,
    int4_ops, and oid_ops</filename>.  You need to add an instance with your
   opclass name (for example, <filename>complex_abs_ops</filename>) to
   <filename>pg_opclass</filename>.  The <filename>oid</filename> of
   this instance is a foreign key in other classes.

   <programlisting>
INSERT INTO pg_opclass (opcname, opcdeftype)
    SELECT 'complex_abs_ops', oid FROM pg_type WHERE typname = 'complex_abs';

SELECT oid, opcname, opcdeftype
    FROM pg_opclass
    WHERE opcname = 'complex_abs_ops';

         +------+-----------------+------------+
         |oid   | opcname         | opcdeftype |
         +------+-----------------+------------+
         |17314 | complex_abs_ops |      29058 |
         +------+-----------------+------------+
   </programlisting>

   Note that the oid for your <filename>pg_opclass</filename> instance will
   be different!  Don't worry about this though.  We'll get this number
   from the system later just like we got the oid of the type here.
  </para>

  <para>
   So now we have an access method and an operator  class.
   We  still  need  a  set of operators; the procedure for
   defining operators was discussed earlier in  this  manual.
   For  the  complex_abs_ops  operator  class on Btrees,
   the operators we require are:

   <programlisting>
        absolute value less-than
        absolute value less-than-or-equal
        absolute value equal
        absolute value greater-than-or-equal
        absolute value greater-than
   </programlisting>
  </para>

  <para>
   Suppose the code that implements the functions  defined
   is stored in the file
   <filename>PGROOT/src/tutorial/complex.c</filename>
  </para>

  <para>
   Part of the code look like this: (note that we will only show the
   equality operator for the rest of the examples.  The other four
   operators are very similar.  Refer to <filename>complex.c</filename>
   or <filename>complex.source</filename> for the details.)

   <programlisting>
#define Mag(c) ((c)-&gt;x*(c)-&gt;x + (c)-&gt;y*(c)-&gt;y)

         bool
         complex_abs_eq(Complex *a, Complex *b)
         {
             double amag = Mag(a), bmag = Mag(b);
             return (amag==bmag);
         }
   </programlisting>
  </para>

  <para>
   There are a couple of important things that are happening below.
  </para>

  <para>
   First, note that operators for less-than, less-than-or equal, equal,
   greater-than-or-equal, and greater-than for <filename>int4</filename>
   are being defined.  All of these operators are already defined for
   <filename>int4</filename> under the names &lt;, &lt;=, =, &gt;=,
   and &gt;. The new operators behave differently, of course.  In order
   to guarantee that <productname>Postgres</productname> uses these
   new operators rather than the old ones, they need to be named differently
   from the old ones.  This is a key point: you can overload operators in
   <productname>Postgres</productname>, but only if the operator isn't
   already defined for the argument types.  That is, if you have &lt;
   defined for (int4, int4), you can't define it again.
   <productname>Postgres</productname> does not check this when you define
   your operator, so be careful.  To avoid this problem, odd names will be
   used for the operators.  If you get this wrong, the access methods
   are likely to crash when you try to do scans.