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/[email protected] -- Alberto Piai Sensational AG Zürich, Switzerland
