Creating Indexes IP and MAC Data Types (followup)

        A while back, I wrote this:

>        I have added the IP and MAC data types from the contrib sources
>to my PostgreSQL v6.3.2 database. These data types seem to work fine when
>none of the fields  that contain these data types are used as indexes.
>Once I turn a field into an index, however, I see the following error
>indication:
>
>        ERROR:  fmgr_info: function 0: cache lookup failed

>        whenever I do a select on that field. For instance, in a table
>called 'hosts' that consists of the following fields:

        and wondered why it was happening. No one was able to tell me,
so now that I've finally figured it out, I thought I'd share. The problem,
as it turned out, was that I had not included the "restrict" and and "join"
phrases (predicates?) in the CREATE OPERATOR SQL commands that created
the operators for these two types. Once I did this, in the manner shown
(but not explained) in the "<Postgres source>/tutorial/complex.sql"
script, the selects started working. Since there is nothing in either the
manuals or the source that I've found that describes these keywords, I
don't know what they do - except for making my database work properly.

        One of the reasons I spent several days flailing about on this
problem was that the tutorial source, while it did mention these keywords,
did not mention that they were necessary or why they were used.Here is
the section of SQL source:

-- the restrict and join selectivity functions are bogus (notice we only
-- have intltsel, eqsel and intgtsel)
CREATE OPERATOR < (
   leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
   restrict = intltsel, join = intltjoinsel
);

        Saying it's bogus is not particularly useful. Something to
the effect of: "You shouldn't have to do this, but you do if you want
indexing to work properly" would have saved me a lot of time. As it was,
I found the comment almost useless, because I would have tried adding
these phrases, anyway, even though I don't know what they do. "Bogus",
being a loosely defined word at best, is not much of a description for
what is being done here.

        I don't want this to sound like a flame, because I'm very grateful
that Postgres is being maintained and expanded by people who know what
they're doing; but please, for the sake of the rest of us, when you're
doing something that doesn't seem to make sense or shouldn't be necessary,
but is, explain why you are doing this in the source. It is frequently
the only way that we can figure out what's going on.

----------------------------------
Date: 15-Oct-98  Time: 15:02:48

Craig Orsinger                  (email: <orsingerc@epg.lewis.army.mil>)
Logicon RDA
Bldg. 8B28                      "Just another megalomaniac with ideas above his
6th & F Streets                 station. The Universe is full of them."
Ft. Lewis, WA   98433                   - The Doctor
----------------------------------