Re: SQL-level pg_datum_image_equal

On Wed, 14 Oct 2026 16:25:33 +0200, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> wrote:
> On Fri, 2 Oct 2026 at 01:34, Alberto Piai <alberto(dot)piai(at)gmail(dot)com> wrote:
> >
> > I wonder what would be the downsides of exposing this function: the
> > notion of image equality is already user-visible through pg_catalog.*=
> > (record_image_eq).
>
> I've thought about it, but I couldn't think of any. Adding
> pg_datum_image_equal actually means that users won't have to go
> through expensive record (de)construction for binary equality, which
> would be a very useful performance improvement.
>
> > One risk of course is users assuming that some values are equal, and
> > then being surprised when more updates happen than expected, in
> > Matthias' use case of data synchronization tools.
>
> What do you mean by "more updates than expected"?
>
> > For pg_datum_image_equal(), we could write something along the lines of
> >
> > This function is intended for specialized purposes such as data
> > synchronization tools. It is not generally useful for writing queries,
> > as it might consider two values as different, even though a comparison
> > with the equality operator would return true.
>
> I don't think this is useful. The distinction is already made
> sufficiently clear in the added docs, and because the function doesn't
> map to indexable operators there's little chance people will
> accidentally use this when they actually want their type's = operator.
>
>
> Kind regards,
>
> Matthias van de Meent
> Databricks (https://www.databricks.com)

Hello Matthias,

This thread caught my eye as I was working on differential selectivity
testing, so I took a look at it. I was able to validate the function
you propose against its intended contract and have a small regression
test that you might want to add, your call.

The contract I checked: for non-NULL values of the same type,
pg_datum_image_equal(a, b) is true iff the type's send() output for a
and b is identical, with NULL vs NULL true and NULL vs non-NULL false.
That is the line between image equality and = that the function exists
to draw, so the test covers the three cases where they diverge:

value-equal, image-unequal: float8 0.0 vs -0.0 ; numeric 1.0 vs 1.00
=, and image-equal: float8 'NaN' vs 'NaN' (float = treats NaN as
equal, so = and the image agree here)

plus a bool_and over int4/int8/text/bool/numeric/float8 that the
function agrees with <type>send() equality on every value pair.

Against v5 (1b91f546b62) every case holds. One wrinkle worth a comment
in the test: a -0.0 literal is folded to +0.0 before it reaches the
function, so to get a genuine negative zero on disk the test routes
values through a table and computes (-1.0::float8) * 0.0; comparing that
to +0.0 correctly reports image-unequal.

Patch attached, as a companion to apply on top of your branch
(src/test/regress/sql/datum_image_equal.sql, added to the misc_functions
parallel group). Fold it in or treat it as a starting point, whichever
you prefer.

best.

-greg