Re: SQL-level pg_datum_image_equal

Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> writes:
> On Wed, 25 Mar 2026 at 22:51, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> You lost me at this part. How does marking the function as STABLE
>> prevent users from persisting things on disk based on the return value
>> of the function? I expected the primary use case for this would be in
>> trigger functions that make decisions about data that goes into
>> tables.

> Indexes and stored generated columns' expression may only contain
> IMMUTABLE functions, so that they don't change output when the inputs
> values are unchanged. As the current datum_image_equal depends on the
> volatile contents/definition of sign-extended bytes (which we clearly
> don't have a defined/expected value for) that makes the output of this
> function not immutable for the "same" input values.

This seems to me to be a rather creative misinterpretation of what
STABLE and IMMUTABLE mean. If you want to claim that IMMUTABLE means
that, then the function isn't STABLE either, since it could give
different results for the "same" input values within one query.
Moreover, switching from IMMUTABLE to STABLE wouldn't fix the
problem of users assuming more than they should.

The actual problem here is that datum_image_eq is assuming more
than it should about the contents of a pass-by-value Datum.
That was okay for its original use-cases because a false not-equal
report would just end in not applying some optimization. But
Memoize thinks that the answers are exact, and users would too
if we expose the function at SQL level.

I think what David proposed at
<CAApHDvreF-UiqBaHtRTQWQ6z1X9snstJW+dfb2DU5GOb-uPEBg(at)mail(dot)gmail(dot)com>
is not a hack, but in fact correcting datum_image_eq/datum_image_hash
to not assume that unspecified bits are reliably the same.

regards, tom lane