Re: equality operator on CIDR column as primary key

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mayers, Philip J" <p(dot)mayers(at)ic(dot)ac(dot)uk>
Cc: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: equality operator on CIDR column as primary key
Date: 2000-08-30 14:55:58
Message-ID: 19663.967647358@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Mayers, Philip J" <p(dot)mayers(at)ic(dot)ac(dot)uk> writes:
> I'm having problems with the CIDR type. I have a table containing a column
> of type CIDR, and that is the primary key. The equality operator for the
> CIDR type appears to only work once per connection.

I'm not quite sure why you are seeing the change in behavior from one
query to the next, but I'm pretty sure of the basic problem: btree
indexes on CIDR/INET types are broken in 7.0.*. The sort comparison
function used by btree yields results that are inconsistent with the
boolean comparison operators (= < etc), which means that when you probe
into the index with a boolean operator you may be looking in the wrong
part of the index :-(. This is fixed in current sources for 7.1, but
in the meantime I'd suggest not creating an index on the CIDR column,
which means not making it a primary key. Or you could hack up
src/backend/utils/adt/network.c to make the boolean operators agree with
network_cmp (easiest way is to make all six of 'em just call network_cmp
and test the sign of the result). If you do that, plan on dropping/
recreating your CIDR indexes afterwards to be sure they are in the right
order.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Normunds 2000-08-30 15:54:51 function
Previous Message Mayers, Philip J 2000-08-30 14:51:16 RE: RE: even more CIDR weirdness (was equality operator on CIDR colum n as primary key)