Re: [HACKERS] Open 6.5 items

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "D'Arcy" "J(dot)M(dot)" Cain <darcy(at)druid(dot)net>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Open 6.5 items
Date: 1999-05-29 15:28:34
Message-ID: 18135.927991714@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"D'Arcy" "J.M." Cain <darcy(at)druid(dot)net> writes:
> And, in fact, that's what happens if you use the operators. The only
> place they are equal is when sorting them so they can't be used as
> primary keys.

Huh? Indexes and operators are the same thing --- or more specifically,
indexes rely on operators to compare keys. I don't see how it's even
*possible* that an index would think that two keys are equal when the
underlying = operator says they are not.

A little experimentation shows that's indeed what's happening, though.
Weird. Is this a deliberate effect, and if so how did you achieve it?
It looks like what could be a serious bug to me.

> I guess there is no argument about the sorting order
> if we think they should be sorted. There is still the question of
> whether or not they should be sorted. There seems to be tacit sgreement
> but could we have a little more discussion. The question is, when inet
> or cidr is used as the primary key on a table, should they be considered
> equal. In fact, think about the question separately as we may want a
> different behaviour for each.

I'd argue that plain indexing ought not try to do anything especially
subtle --- in particular it ought not vary from the behavior of the
comparison operators for the type. If someone wants a table wherein you
can't enter two spellings of the same hostname, the right way would be
to construct a unique functional index using a function that reduces the
INET type into the simpler form. A good analogy might be a text field
where you don't want any two entries to be equal on a case-insensitive
basis. You don't up and change the behavior of indexing to be
case-insensitive, you say
CREATE UNIQUE INDEX foo_f1_key ON foo (lower(f1) text_ops);

regards, tom lane

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 1999-05-29 15:47:24 Re: [HACKERS] Open 6.5 items
Previous Message Tom Lane 1999-05-29 15:03:04 Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/backend/storage/buffer bufmgr.c'