Re: SQL-level pg_datum_image_equal

Hi all,

On Mon, Aug 10, 2026 at 12:32 PM Matthias van de Meent
<boekewurm+postgres(at)gmail(dot)com> wrote:
>
> Here's version 3 of the patch, which is no more than a rebase.
>
> Short recap: pg_datum_image_equal(any, any) -> bool allows
> synchronization tools to reliably detect that colum values have not
> changed in any way, for every type. Without this function, e.g.
> numeric and jsonb columns wouldn't have a correct strict equality test
> on the type itself (you'd have to cast to string and use a C-collated
> equality against the string representation, which is relatively more
> expensive). With this new function the UPDATE operation can use CASE
> WHEN <equal> THEN old_val ELSE new_val END CASE; thus skipping
> re-toasting the new value, reducing TOAST churn and also avoiding some
> unneeded non-HOT updates in such synchronization workloads.
>
>
> Kind regards,
>
> Matthias van de Meent
> Databricks (https://www.databricks.com)
>
> p.s. The upthread issues with datum_image_*() have been resolved in 0d866282b8.

I reviewed and tested the v3 patch. I like the idea of exposing the
existing datum_image_eq() functionality through a SQL-level function,
pg_datum_image_equal(anyelement, anyelement). I think this can be
useful, especially for synchronization/update scenarios where we need
to check whether two values have the same datum representation and
potentially avoid unnecessary work. So, +1 from my side for
introducing this function. I applied the patch cleanly and tested it
on the current tree. I tested the function with different cases: Basic
equal and unequal integer values, NULL vs NULL and NULL vs non-NULL,
numeric values such as 1.0 and 1.00, where normal SQL equality returns
true but pg_datum_image_equal() correctly returns false, The
previously discussed hash_numeric() case with both MATERIALIZED and
non-materialized CTEs, Both cases returned the same result, so I could
not reproduce the earlier sign-extension issue on the current tree,
Composite values, arrays, and domains, Large TOASTed text and bytea
values, Polymorphic type resolution, including the expected error for
unknown literals, The CASE-based usage described for synchronization
scenarios, where the existing value can be retained when the datum
images are equal. Overall, I did not find any functional issue with
the current implementation.
But I do have a couple of suggestions that may make the patch even stronger:
1. Add behavioral regression tests - At present, the regression change
appears to cover the presence of the function through opr_sanity, but
there are no dedicated SQL-level tests covering its actual behavior.
It would be useful to add tests for basic equality/inequality, NULL
handling, representation-sensitive values such as numeric, and the
previously reported hash_numeric() MATERIALIZED vs non-materialized
case. The latter would also help ensure that the sign-extension issue
that was previously discussed does not regress.
2. Slightly expand the documentation - It may be useful to explicitly
mention that pg_datum_image_equal() is different from the normal SQL
equality operator. For example, 1.0::numeric and 1.00::numeric compare
equal using =, but their datum images are different and the new
function returns false. It may also be worth documenting the NULL
behavior, since two NULL arguments return true.
Other than these suggestions, the implementation looks straightforward
to me and makes good use of the existing datum_image_eq()
infrastructure. Overall, I think this is a useful addition and +1 from
my side.

Regards,
Solai