Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group