pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Creating Indexes for MAC and IP Data Types
4+ messages / 3 participants
[nested] [flat]

* Creating Indexes for MAC and IP Data Types
@ 1998-09-05 00:15  Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>
  0 siblings, 2 replies; 4+ messages in thread

From: Craig Orsinger @ 1998-09-05 00:15 UTC (permalink / raw)
  To: 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
----------------------------------

Attachments:

  mpn_db_build.log (application/octet-stream, 21.6K) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Creating Indexes for MAC and IP Data Types
@ 1998-09-05 01:47  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>
  1 sibling, 0 replies; 4+ messages in thread

From: Bruce Momjian @ 1998-09-05 01:47 UTC (permalink / raw)
  To: orsingerc@epg-gw1.lewis.army.mil; +Cc: 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:

We are working on integrating this type into the type system for 6.4.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Creating Indexes for MAC and IP Data Types
@ 1998-09-05 04:33  The Hermit Hacker <scrappy@hub.org>
  parent: Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>
  1 sibling, 1 reply; 4+ messages in thread

From: The Hermit Hacker @ 1998-09-05 04:33 UTC (permalink / raw)
  To: Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>; +Cc: pgsql-sql

On Fri, 4 Sep 1998, Craig Orsinger wrote:

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

	v6.4 will hopefully see part of this merged into the standard
types available, including indexing...we are currently discussing this...

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Creating Indexes for MAC and IP Data Types
@ 1998-09-08 17:22  Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>
  parent: The Hermit Hacker <scrappy@hub.org>
  0 siblings, 0 replies; 4+ messages in thread

From: Craig Orsinger @ 1998-09-08 17:22 UTC (permalink / raw)
  To: pgsql-sql


On 05-Sep-98 The Hermit Hacker wrote:
> On Fri, 4 Sep 1998, Craig Orsinger wrote:
> 
>>         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:
> 
>       v6.4 will hopefully see part of this merged into the standard
> types available, including indexing...we are currently discussing this...

        That sounds like a good idea, although in my case I'd still need
to use a modified version of the IP type. In the meantime, any ideas what
might be causing this problem?

----------------------------------
Date: 08-Sep-98  Time: 10:20:11

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1998-09-08 17:22 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-05 00:15 Creating Indexes for MAC and IP Data Types Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>
1998-09-05 01:47 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-09-05 04:33 ` The Hermit Hacker <scrappy@hub.org>
1998-09-08 17:22   ` Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox