Thread: Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

CREATE TABLE IF NOT EXISTS test1
( col1 POINT
)
WITH (
OIDS =FALSE
);

INSERT INTO test1 VALUES ('(0,1)');

CREATE OR REPLACE FUNCTION test_update() RETURNS TRIGGER AS $BODY$ BEGIN
   RETURN NEW;
 END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER trigger_update_test
AFTER UPDATE
ON test1
FOR EACH ROW
WHEN ((old.* IS DISTINCT FROM new.*))
EXECUTE PROCEDURE test_update();

UPDATE test1 SET col1 = '(9,0)';

pinker wrote:
> I've tried to write audit trigger which fires only when data changed, so I used "WHEN (OLD.* IS
> DISTINCT FROM NEW.*)" clause as described in documentation
> <http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html> . Should this clause be independent
> from data type? because an error occurs when I'm trying to modify row with point data type: ERROR:
> could not identify an equality operator for type point

I guess it is dependent on data type as it requires an equality operator,
and type "point" doesn't have one.

You'd have to hand-roll a comparison in this case, probably using the
"same as" operator "~=".

Yours,
Laurenz Albe

On 09/17/2015 06:32 AM, Albe Laurenz wrote:
> pinker wrote:
>> I've tried to write audit trigger which fires only when data changed, so I used "WHEN (OLD.* IS
>> DISTINCT FROM NEW.*)" clause as described in documentation
>> <http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html> . Should this clause be independent
>> from data type? because an error occurs when I'm trying to modify row with point data type: ERROR:
>> could not identify an equality operator for type point
>
> I guess it is dependent on data type as it requires an equality operator,
> and type "point" doesn't have one.

To echo the OP, why is that?

http://www.postgresql.org/docs/9.4/interactive/functions-comparison.html

For non-null inputs, IS DISTINCT FROM is the same as the <> operator.

So:

test=> select '(1,2)'::point <> '(1,2)'::point;
  ?column?
----------
  f
(1 row)

test=> select '(1,2)'::point <> '(1,3)'::point;
  ?column?
----------
  t

test=> select '(1,2)'::point <> null;
  ?column?
----------

(1 row)


>
> You'd have to hand-roll a comparison in this case, probably using the
> "same as" operator "~=".

I could force it to work by casting:

test=> select '(1,2)'::point::text is distinct from '(1,2)'::point::text;
  ?column?
----------
  f
(1 row)

test=> select '(1,2)'::point::text is distinct from '(1,3)'::point::text;
  ?column?
----------
  t
(1 row)

test=> select '(1,2)'::point::text is distinct from null;
  ?column?
----------
  t

>
> Yours,
> Laurenz Albe
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Yes I will try to do something else like casting, hashing or using another
operator but in documentation this example stands as a model audit trigger
without any warnings...




--
View this message in context:
http://postgresql.nabble.com/Shouldn-t-WHEN-OLD-IS-DISTINCT-FROM-NEW-clause-be-independent-from-data-type-tp5866276p5866286.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 09/17/2015 06:32 AM, Albe Laurenz wrote:
>> I guess it is dependent on data type as it requires an equality operator,
>> and type "point" doesn't have one.

> To echo the OP, why is that?
> http://www.postgresql.org/docs/9.4/interactive/functions-comparison.html
> For non-null inputs, IS DISTINCT FROM is the same as the <> operator.

Well, that's true: the parser actually looks up the operator named "<>"
for the given data types, and IS DISTINCT FROM is just a prefilter on
that to do the right thing with nulls.  So because type point has an
operator that's physically named "<>", that case works.

However, in the given case, what gets found for "<>" is record_ne().
The record comparison functions apply btree comparison functions for
the individual column datatypes in the record --- and point does not
have a btree opclass.

If memory serves, for equal/not-equal comparisons a hash opclass would
work too, but point does not have that either.

Since type record *does* have btree/hash opclasses, it is not negotiable
that the component column types obey btree or at least hash semantics.
The only way to fix this would be to provide such opclasses for point.
Btree has the probably-fatal obstacle that there's no plausible linear
sort order for 2-D points.  It would be possible to make hash work, if
it weren't that point_eq() is fuzzy equality not exact equality.

            regards, tom lane

On 09/17/2015 06:54 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 09/17/2015 06:32 AM, Albe Laurenz wrote:
>>> I guess it is dependent on data type as it requires an equality operator,
>>> and type "point" doesn't have one.
>
>> To echo the OP, why is that?
>> http://www.postgresql.org/docs/9.4/interactive/functions-comparison.html
>> For non-null inputs, IS DISTINCT FROM is the same as the <> operator.
>
> Well, that's true: the parser actually looks up the operator named "<>"
> for the given data types, and IS DISTINCT FROM is just a prefilter on
> that to do the right thing with nulls.  So because type point has an
> operator that's physically named "<>", that case works.

If you use '<>' explicitly, otherwise:

test=> select '(1,2)'::point is distinct from '(1,3)'::point;
ERROR:  operator does not exist: point = point
LINE 1: select '(1,2)'::point is distinct from '(1,3)'::point;

 From the docs I would have expected the same behavior as:

test=> select '(1,2)'::point <> '(1,3)'::point;
  ?column?
----------
  t

Is this expected?

If so, should the docs be changed to reflect?

If the docs need changing how does one go about that?

>
> However, in the given case, what gets found for "<>" is record_ne().
> The record comparison functions apply btree comparison functions for
> the individual column datatypes in the record --- and point does not
> have a btree opclass.

Aah, so in the TRIGGER this happen because of the OLD.*, NEW.* record
comparison.

>
> If memory serves, for equal/not-equal comparisons a hash opclass would
> work too, but point does not have that either.
>
> Since type record *does* have btree/hash opclasses, it is not negotiable
> that the component column types obey btree or at least hash semantics.
> The only way to fix this would be to provide such opclasses for point.
> Btree has the probably-fatal obstacle that there's no plausible linear
> sort order for 2-D points.  It would be possible to make hash work, if
> it weren't that point_eq() is fuzzy equality not exact equality.
>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 09/17/2015 06:54 AM, Tom Lane wrote:
>> Well, that's true: the parser actually looks up the operator named "<>"
>> for the given data types, and IS DISTINCT FROM is just a prefilter on
>> that to do the right thing with nulls.  So because type point has an
>> operator that's physically named "<>", that case works.

> If you use '<>' explicitly, otherwise:
> test=> select '(1,2)'::point is distinct from '(1,3)'::point;
> ERROR:  operator does not exist: point = point

Ah, sorry, actually what IS [NOT] DISTINCT FROM looks up is the "="
operator.  The core point remains, though, that this is a name-based
lookup rather than an opclass-based one.  I'd like to get us moved
over to using opclass-based lookups for all cases where the system
currently assumes that operators named "=" or "<>" necessarily behave
in a particular way.  However, that would leave point and some of the
other weirder datatypes even further out in the cold than they are now.

            regards, tom lane

On 09/17/2015 07:34 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 09/17/2015 06:54 AM, Tom Lane wrote:
>>> Well, that's true: the parser actually looks up the operator named "<>"
>>> for the given data types, and IS DISTINCT FROM is just a prefilter on
>>> that to do the right thing with nulls.  So because type point has an
>>> operator that's physically named "<>", that case works.
>
>> If you use '<>' explicitly, otherwise:
>> test=> select '(1,2)'::point is distinct from '(1,3)'::point;
>> ERROR:  operator does not exist: point = point
>
> Ah, sorry, actually what IS [NOT] DISTINCT FROM looks up is the "="
> operator.

So the docs should be changed?

As they stand now:

http://www.postgresql.org/docs/9.4/interactive/functions-comparison.html

"For non-null inputs, IS DISTINCT FROM is the same as the <> operator. "

That did not indicate to me the '=' was important to IS DISTINCT ON.
Then again I may need more attention from the clue stick:)

  The core point remains, though, that this is a name-based
> lookup rather than an opclass-based one.  I'd like to get us moved
> over to using opclass-based lookups for all cases where the system
> currently assumes that operators named "=" or "<>" necessarily behave
> in a particular way.  However, that would leave point and some of the
> other weirder datatypes even further out in the cold than they are now.
>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 09/17/2015 07:34 AM, Tom Lane wrote:
>> Ah, sorry, actually what IS [NOT] DISTINCT FROM looks up is the "="
>> operator.

> So the docs should be changed?
> As they stand now:
> http://www.postgresql.org/docs/9.4/interactive/functions-comparison.html
> "For non-null inputs, IS DISTINCT FROM is the same as the <> operator. "

Well, it's an oversimplification to begin with.  Do we want to try to
cram all of these implementation details in there?

            regards, tom lane

On 09/17/2015 07:51 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 09/17/2015 07:34 AM, Tom Lane wrote:
>>> Ah, sorry, actually what IS [NOT] DISTINCT FROM looks up is the "="
>>> operator.
>
>> So the docs should be changed?
>> As they stand now:
>> http://www.postgresql.org/docs/9.4/interactive/functions-comparison.html
>> "For non-null inputs, IS DISTINCT FROM is the same as the <> operator."
>
> Well, it's an oversimplification to begin with.  Do we want to try to
> cram all of these implementation details in there?

No, just that IS DISTINCT ON is dependent on '=' and will not work on
data types that do not have that operator. That would also cover the IS
NOT DISTINCT FROM case where '=' is the operator 'alias'.

>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Tom Lane wrote:

> Since type record *does* have btree/hash opclasses, it is not negotiable
> that the component column types obey btree or at least hash semantics.
> The only way to fix this would be to provide such opclasses for point.
> Btree has the probably-fatal obstacle that there's no plausible linear
> sort order for 2-D points.  It would be possible to make hash work, if
> it weren't that point_eq() is fuzzy equality not exact equality.

This comment made me remember something I mentioned some months ago,
regarding fuzzy comparisons for other datatypes;
http://www.postgresql.org/message-id/20150512181307.GJ2523@alvh.no-ip.org
It seems that currently box_contains_pt() uses exact floating point
operators rather than fuzzy; we were pushing for having some of these
operators be changed to use the fuzzy definitions, and introduce a few
more fuzzy operators, to be used in BRIN opclasses.

I wonder if it would make sense to introduce a hash opclass for point
and other floating-point-based datatypes that uses strict equality, so
that IS [NOT] DISTINCT FROM works on them.  Since there cannot be a
btree opclass for point, there shouldn't be any definitional problem in
doing so; but for datatypes that do have a linear sort order, maybe this
(having different equality operators for btree and hash) would be a
problem.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> Since type record *does* have btree/hash opclasses, it is not negotiable
>> that the component column types obey btree or at least hash semantics.
>> The only way to fix this would be to provide such opclasses for point.
>> Btree has the probably-fatal obstacle that there's no plausible linear
>> sort order for 2-D points.  It would be possible to make hash work, if
>> it weren't that point_eq() is fuzzy equality not exact equality.

> This comment made me remember something I mentioned some months ago,
> regarding fuzzy comparisons for other datatypes;
> http://www.postgresql.org/message-id/20150512181307.GJ2523@alvh.no-ip.org
> It seems that currently box_contains_pt() uses exact floating point
> operators rather than fuzzy; we were pushing for having some of these
> operators be changed to use the fuzzy definitions, and introduce a few
> more fuzzy operators, to be used in BRIN opclasses.

> I wonder if it would make sense to introduce a hash opclass for point
> and other floating-point-based datatypes that uses strict equality, so
> that IS [NOT] DISTINCT FROM works on them.  Since there cannot be a
> btree opclass for point, there shouldn't be any definitional problem in
> doing so; but for datatypes that do have a linear sort order, maybe this
> (having different equality operators for btree and hash) would be a
> problem.

The other line of attack would be to deprecate all the fuzzy comparison
behavior in the geometric types, and just make it exact.  Don't know how
much pain that would add, but surely it would also subtract some.

            regards, tom lane

pinker <pinker@onet.eu> wrote:

> I've tried to write audit trigger which fires only when data
> changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause
> as described in documentation. Should this clause be independent
> from data type? because an error occurs when I'm trying to modify
> row with point data type:
> ERROR: could not identify an equality operator for type point

> CREATE TRIGGER trigger_update_test
> AFTER UPDATE
> ON test1
> FOR EACH ROW
> WHEN ((old.* IS DISTINCT FROM new.*))
> EXECUTE PROCEDURE test_update();

Since you seem to be on 9.4, how about this?:

CREATE TRIGGER trigger_update_test
AFTER UPDATE
ON test1
FOR EACH ROW
WHEN ((old *<> new))
EXECUTE PROCEDURE test_update();

http://www.postgresql.org/docs/9.4/static/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON

Which says (in part):

| To support matching of rows which include elements without a
| default B-tree operator class, the following operators are
| defined for composite type comparison: *=, *<>, *<, *<=, *>, and
| *>=. These operators compare the internal binary representation
| of the two rows. Two rows might have a different binary
| representation even though comparisons of the two rows with the
| equality operator is true. The ordering of rows under these
| comparison operators is deterministic but not otherwise
| meaningful. These operators are used internally for materialized
| views and might be useful for other specialized purposes such as
| replication but are not intended to be generally useful for
| writing queries.

It seems to me that auditing would be an appropriate use, because
it would show whether there was any change in the stored value, not
just whether the old and new values were equal in a btree ordering
comparison.  For example, if a citext column were changed from 'a'
to 'A', it would compare as equal with its type's "=" operator, but
the row would show as changed anyway, if you use "*=" or "*<>".

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

Hi,

Would you please provide the link to the section in the documentation that you are referring to because I'm new to PostgreSQL and I didn't know WHEN could be used outside of CASE and EXCEPTION blocks.

> The other line of attack would be to deprecate all the fuzzy comparison
> behavior in the geometric types, and just make it exact.  Don't know how
> much pain that would add, but surely it would also subtract some.

How can we go forward about this?  The current state of the operators
stand in the way of improving index support for them.

On 10/4/15 4:16 AM, Emre Hasegeli wrote:
>> The other line of attack would be to deprecate all the fuzzy comparison
>> behavior in the geometric types, and just make it exact.  Don't know how
>> much pain that would add, but surely it would also subtract some.
>
> How can we go forward about this?  The current state of the operators
> stand in the way of improving index support for them.

Your easiest way forward right now is probably to create an exact
equality operator for point and the relevant hash opclass. That should
be enough to let IS DISTINCT do it's thing.

BTW, it occurred to me that we could create btree opclasses as well, if
we explicitly compared X before Y (or vice-versa). I certainly wouldn't
call those operators < or >, but maybe there's enough use to supporting
btree for this to make sense.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com