Creating Indexes for MAC and IP Data Types - Mailing list pgsql-sql

        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:

        name    char(32)
        mac     macaddr
        ip      ipaddr
        status  integer
        time    integer

        the query:

        SELECT * FROM hosts WHERE mac='00:00:0c:00:00:01' ;

        works fine as long as no index is defined for the 'mac' field, as do
all the other operations ('<', '<=', etc.). Once an index is created for
this field, say with the command:

        CREATE UNIQUE INDEX hosts_mac_ix ON hosts USING btree(mac macaddr_ops) ;

        running the SELECT statement above will cause the error message
to appear for every operation except '<>'.

        I based the installation procedure on the 'complex.sql' installation
script in the tutorial source. No errors occur during the installation (a
log is attached to this message - I ran 'psql' in batch mode with the '-e'
flag set so the queries to the backend would be printed out).

        Ironically, the one operation that is not referred to by the
procedure in 'complex.sql' is the '<>' operation, the only one that still
works with the index created. Poking around in the backend code, I found
that the error message is generated by a function named 'HeapTupleIsValid()'
deciding that a tuple (presumably for function # 0) is not valid. The
function that calls 'HeapTupleIsValid()', which is named 'fmgr_info()', is
in the file backend/utils/fmgr/fmgr.c in the source.

        Everything I said about the MAC data type applies to the IP data
type, as well. Whatever I'm doing, at least I'm consistent ...

        So, my questions are:

        Has anyone done this successfully? If so, can you share the SQL
code you used to install the operators?

        If no one has done this for these data types, then what tuple is
this function referring to, and where does it find the procedure ID # 0?

        Does anyone have suggestions as to how to chase down this problem?

        Oh, yeah. I'm running PostgreSQL on a Red Hat Linux 5.1 system,
in case that means anything. It's the latest contributed RPM, version
6.3.2-6. I had the 6.3.2-4 version installed for a while, and the same
problem occurs on that version.

----------------------------------
Date: 04-Sep-98  Time: 16:23:22

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
----------------------------------