Re: record identical operator

Stephen Frost <sfrost(at)snowman(dot)net>
> Kevin Grittner  wrote:
>> Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>>
>>> If it's not actually *changing* (wrt its value), then I'm not
>>> at all impressed with the notion that it's going to get updated
>>> anyway.
>>
>> But PostgreSQL very specifically (and as far as I can tell
>> *intentionally*) allows you to *change* a value and have it
>> still be considered *equal*.  
>
> I'm curious where you're going with that- of course you can
> update a value and have the same value (and possibly the same
> byte representation) stored over the old.

The way I see it, you can update a column to a different value
which will compare as equal.  That's fine.  Nobody wants to change
that.  But it is still not the same value.

>> The concept of equal values really means
>> more like "equivalent" or "close enough" for common purposes.  It
>> very specifically does *not* mean the same value.
>
> I'm really curious about your thoughts on unique indexes then.
> Should two numerics which are the same value but different byte
> representations be allowed in a unique index?

Not if it is defined with the default opclass, which will use an
equal operator.  Of course, this patch would allow an index on a
record to be defined with record_image_ops, in which case it would
sort by the raw bytes in the values of the record.  That's not
going to be useful in very many places, which is why it would not
be the default.  You don't get that behavior unless you ask for it.

See this docs page for a similar example related to complex numbers:

http://www.postgresql.org/docs/current/interactive/xindex.html#XINDEX-EXAMPLE

> If the type operator says they're equal, then I think we need to
> consider them as equal.

Absolutely.  Two different values may be equal within an opclass.

> If an update happens with a conditional of:
>
> where col1 = 'Abc'
>
> When col1 is 'ABC' using citext, should we still issue the
> update?

Absolutely not, because the update was requested in the case that
the equality test was true.  Yet if a row is updated to replace
'Abc' with 'ABC', then streaming replication should copy the
"different but equal value" (it does), a normal view should now
show 'ABC' (it does), and a refresh of a matview should cause the
matview to show 'ABC' (it doesn't in git, but this patch would make
that work).

> The value *can* be changed to be equal to the existing value but
> that doesn't make the two values *not equal*.

Nobody has ever argued that they should be considered *not equal*.
It's just about providing a way to recognize when two equal values
*are not the same*.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company