Re: SQL-level pg_datum_image_equal

Hi,

I might have a second use case for the proposed pg_datum_image_equal().
Over at [0], I'm trying to implement an ALTER TABLE command which relies
on a CHECK constraint to prove that the transformation it intends to do
is correct.

Initially I thought it would be enough to rely on equality, but as it
turns out it isn't: we need image equality.

Requiring a constraint which makes use of pg_datum_image_equal() would
be a rather elegant way to let the user prove that the backfilled data
is correct, allowing us to safely skip a costly table rewrite during
ALTER TABLE. It would also allow us to support the operation for types
which don't implement equalimage.

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).

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.

Maybe it would be enough to spell this out more clearly in the
documentation?

Section 9.26.6 about record type comparison says:
(https://www.postgresql.org/docs/19/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON)

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 and B-Tree deduplication (see Section 65.1.4.3).
They are not intended to be generally useful for writing queries,
though.

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 might be biased because numeric is exactly what I was dealing with,
but I find the given example with numeric '1.0' and '1.00' clear enough.

Regards,

Alberto

[0] https://postgr.es/m/DLTW5NS7XJA0.1Y3GAC6AK8N2H@gmail.com

--
Alberto Piai
Sensational AG
Zürich, Switzerland