MIME-Version: 1.0
References: 
 <CAEze2WhsqYjg0oGY+7yooimUK7zRc9PY9u8u-Oo=VmJ+DAAkKg@mail.gmail.com>
 <CACJufxFV5KqmF86upFmX2tPzfKMWjWNf8+uYiYufLKatOsYXQw@mail.gmail.com>
 <CAEze2Wg4Fj+9LQOv=J5cqwapD8vz3oDh1B0iyX7RwesvseH9gg@mail.gmail.com>
 <CAEze2WjUyYhcUwzaPiQbe-xBB_knbnG8Xr_9ACL7HrGVx=Vydw@mail.gmail.com>
 <CAApHDvpC8sj-ayXMQsh5DfnZV+X2QtUdvQ3Qr=81bcyaW7gVFw@mail.gmail.com>
 <CAEze2WhK4ko6YTVV10iVo8sna0s=pdFYJ2xPrNNwxc9PqJJZoA@mail.gmail.com>
 <CAApHDvo0D4HjEGWZ8XJ0RBOFzo7wZs7r9DogrhnH_Hq7gNRBQQ@mail.gmail.com>
 <CAEze2Whtif9qh-TXX52sCj4T8vuYfciOjJnSfmmTG4B+aFuQiA@mail.gmail.com>
 <1010250.1774545419@sss.pgh.pa.us>
In-Reply-To: <1010250.1774545419@sss.pgh.pa.us>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Fri, 27 Mar 2026 15:51:35 +0100
Message-ID: 
 <CAEze2WjF9M8TFG3q1z80bvN0_CijHSGF_NfEhvjqjbMnOOTJGg@mail.gmail.com>
Subject: Re: SQL-level pg_datum_image_equal
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <dgrowleyml@gmail.com>,
 jian he <jian.universality@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEze2WjF9M8TFG3q1z80bvN0_CijHSGF_NfEhvjqjbMnOOTJGg%40mail.gmail.com>
Precedence: bulk

On Thu, 26 Mar 2026 at 18:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
> > On Wed, 25 Mar 2026 at 22:51, David Rowley <dgrowleyml@gmail.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.

Yeah, that's fair.

> 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@mail.gmail.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.

Agreed.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)