MIME-Version: 1.0
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Wed, 10 Dec 2025 18:46:03 +0100
Message-ID: 
 <CAEze2WhsqYjg0oGY+7yooimUK7zRc9PY9u8u-Oo=VmJ+DAAkKg@mail.gmail.com>
Subject: SQL-level pg_datum_image_equal
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000d9494c06459c9a13"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEze2WhsqYjg0oGY%2B7yooimUK7zRc9PY9u8u-Oo%3DVmJ%2BDAAkKg%40mail.gmail.com>
Precedence: bulk

--000000000000d9494c06459c9a13
Content-Type: text/plain; charset="UTF-8"

Hi,

One of our customers has this workload where every so often they
update the whole table to make sure it's up-to-date. In general, you'd
probably want to use MERGE for such a workload and ignore all rows
that already have only matching data, but there's a catch: PostgreSQL
doesn't have an efficient way to check if the provided data is
actually equal in all senses of the word, so we can't easily and
cheaply determine whether an update is needed; which is one reason why
the full table was updated every time.

A naive approach to determining whether each value needs to be updated
would use `old IS NOT DISTINCT FROM new`, but a.) this relies on `=`
operators to exist for that type, and b.) the = operator of some types
don't always distinguish between values that are different for human
readers; with as famous example '1.0' and '1.00' in numeric; they have
an equal value but are clearly distinct to readers (and certain
functions).

One could get around this in this case by 'simply' casting to text and
comparing the outputs (using the C collation for performance and
determinism), or by wrapping it in a row (which then uses
record_image_eq, which does use binary compare functions internally),
but both imply additional parsing, wrapping, and overhead compared to
a direct datum_image_eq call.

So, attached is a simple and to-the-point patch that adds the function
mentioned in $subject, which will tell the user whether two values of
the same type have an exactly equal binary representation, using
datum_image_eq.


Kind regards,

Matthias van de Meent

--000000000000d9494c06459c9a13
Content-Type: application/octet-stream;
	name="v1-0001-Add-SQL-level-datum-equality-tests.patch"
Content-Disposition: attachment;
	filename="v1-0001-Add-SQL-level-datum-equality-tests.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mj0av9kl0>
X-Attachment-Id: f_mj0av9kl0

RnJvbSBmNTI2NDM2OGEwNWJiNDliNGNlMGU0ZmY1ODZmYjVkYTZiMWY1Y2M0IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNYXR0aGlhcyB2YW4gZGUgTWVlbnQgPGJvZWtld3VybStwb3N0
Z3Jlc0BnbWFpbC5jb20+CkRhdGU6IFdlZCwgMTAgRGVjIDIwMjUgMTg6NDU6MTkgKzAxMDAKU3Vi
amVjdDogW1BBVENIIHYxXSBBZGQgU1FMLWxldmVsIGRhdHVtIGVxdWFsaXR5IHRlc3RzCgpUaGlz
IGVuYWJsZXMgaW1wcm92ZWQgcGVyZm9ybWFuY2UgZm9yIHVzZXJzIHRoYXQgbmVlZCB0byB0ZXN0
IGZvcgpleGFjdCBieXRld2lzZSBkaWZmZXJlbmNlcyBpbiBTUUw7IGUuZy4gdG8gc2VlIGlmIGFu
IFVQREFURSBpcwpyZWFsbHkgbmVjZXNzYXJ5IGZvciBleHRlcm5hbGx5IHByb3ZpZGVkIHZhbHVl
cy4KCkEgd29ya2Fyb3VuZCBhcm91bmQgdGhpcyBsaW1pdGF0aW9uIHdhcyBwb3NzaWJsZSB0aHJv
dWdoIHZhcmlvdXMKbWV0aG9kcywgYnV0IGEgZ2VuZXJpYyBtZXRob2Qgd2FzIG5vdCBhdmFpbGFi
bGUgZm9yIGFsbCB0eXBlcy4KLS0tCiBzcmMvYmFja2VuZC91dGlscy9hZHQvcHNldWRvdHlwZXMu
YyAgICAgIHwgNTEgKysrKysrKysrKysrKysrKysrKysrKysrCiBzcmMvaW5jbHVkZS9jYXRhbG9n
L3BnX3Byb2MuZGF0ICAgICAgICAgIHwgIDcgKysrKwogc3JjL3Rlc3QvcmVncmVzcy9leHBlY3Rl
ZC9vcHJfc2FuaXR5Lm91dCB8ICAxICsKIDMgZmlsZXMgY2hhbmdlZCwgNTkgaW5zZXJ0aW9ucygr
KQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9wc2V1ZG90eXBlcy5jIGIvc3Jj
L2JhY2tlbmQvdXRpbHMvYWR0L3BzZXVkb3R5cGVzLmMKaW5kZXggMzE3YTFmMmIyODIuLjIxNmQ4
OTVkMmY3IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9hZHQvcHNldWRvdHlwZXMuYwor
KysgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvcHNldWRvdHlwZXMuYwpAQCAtMjMsNyArMjMsOSBA
QAogI2luY2x1ZGUgInBvc3RncmVzLmgiCiAKICNpbmNsdWRlICJsaWJwcS9wcWZvcm1hdC5oIgor
I2luY2x1ZGUgInV0aWxzL2RhdHVtLmgiCiAjaW5jbHVkZSAidXRpbHMvZm1ncnByb3Rvcy5oIgor
I2luY2x1ZGUgInV0aWxzL2xzeXNjYWNoZS5oIgogCiAKIC8qCkBAIC0zNzUsMyArMzc3LDUyIEBA
IFBTRVVET1RZUEVfRFVNTVlfSU9fRlVOQ1MoYW55ZWxlbWVudCk7CiBQU0VVRE9UWVBFX0RVTU1Z
X0lPX0ZVTkNTKGFueW5vbmFycmF5KTsKIFBTRVVET1RZUEVfRFVNTVlfSU9fRlVOQ1MoYW55Y29t
cGF0aWJsZSk7CiBQU0VVRE9UWVBFX0RVTU1ZX0lPX0ZVTkNTKGFueWNvbXBhdGlibGVub25hcnJh
eSk7CisKKy8qCisgKiBDb21wYXJlcyB0d28gZGF0dW1zIG9mIHRoZSBzYW1lIChhbnkpIHR5cGUs
IGFuZCByZXR1cm5zIHdoZXRoZXIgdGhleSBoYXZlCisgKiB0aGUgc2FtZSBiaW5hcnkgcmVwcmVz
ZW50YXRpb24uCisgKi8KK0RhdHVtCitwZ19kYXR1bV9pbWFnZV9lcXVhbChQR19GVU5DVElPTl9B
UkdTKQoreworCWJvb2wJCWVxOworCisJaWYgKFBHX0FSR0lTTlVMTCgwKSAhPSBQR19BUkdJU05V
TEwoMSkpCisJeworCQllcSA9IGZhbHNlOworCX0KKwllbHNlIGlmIChQR19BUkdJU05VTEwoMCkp
CisJeworCQkvKiBib3RoIE5VTEwgKi8KKwkJZXEgPSB0cnVlOworCX0KKwllbHNlCisJeworCQlP
aWQJCXR5cDsKKwkJRGF0dW0JYXJnMDsKKwkJRGF0dW0JYXJnMTsKKwkJYm9vbAl0eXBieXZhbDsK
KwkJY2hhcgl0eXBhbGlnbjsKKwkJaW50MTYJdHlwbGVuOworCisJCXR5cCA9IGdldF9mbl9leHBy
X2FyZ3R5cGUoZmNpbmZvLT5mbGluZm8sIDApOworCisJCWlmICghT2lkSXNWYWxpZCh0eXApKQor
CQl7CisJCQllcmVwb3J0KEVSUk9SLAorCQkJCQkoZXJyY29kZShFUlJDT0RFX0ZFQVRVUkVfTk9U
X1NVUFBPUlRFRCksCisJCQkJCSBlcnJtc2coImNvdWxkIG5vdCBkZXRlcm1pbmUgdHlwZSIpKSk7
CisJCX0KKworCQlBc3NlcnQodHlwID09IGdldF9mbl9leHByX2FyZ3R5cGUoZmNpbmZvLT5mbGlu
Zm8sIDEpKTsKKworCQlhcmcwID0gUEdfR0VUQVJHX0RBVFVNKDApOworCQlhcmcxID0gUEdfR0VU
QVJHX0RBVFVNKDEpOworCisJCWdldF90eXBsZW5ieXZhbGFsaWduKHR5cCwgJnR5cGxlbiwgJnR5
cGJ5dmFsLCAmdHlwYWxpZ24pOworCisJCWVxID0gZGF0dW1faW1hZ2VfZXEoYXJnMCwgYXJnMSwg
dHlwYnl2YWwsIHR5cGxlbik7CisJfQorCisJUEdfUkVUVVJOX0JPT0woZXEpOworfQpkaWZmIC0t
Z2l0IGEvc3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19wcm9jLmRhdCBiL3NyYy9pbmNsdWRlL2NhdGFs
b2cvcGdfcHJvYy5kYXQKaW5kZXggZmQ5NDQ4ZWM3YjkuLjJmMWIxYmE4MzcwIDEwMDY0NAotLS0g
YS9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3Byb2MuZGF0CisrKyBiL3NyYy9pbmNsdWRlL2NhdGFs
b2cvcGdfcHJvYy5kYXQKQEAgLTEyNjEyLDQgKzEyNjEyLDExIEBACiAgIHByb2FyZ25hbWVzID0+
ICd7cGlkLGlvX2lkLGlvX2dlbmVyYXRpb24sc3RhdGUsb3BlcmF0aW9uLG9mZixsZW5ndGgsdGFy
Z2V0LGhhbmRsZV9kYXRhX2xlbixyYXdfcmVzdWx0LHJlc3VsdCx0YXJnZXRfZGVzYyxmX3N5bmMs
Zl9sb2NhbG1lbSxmX2J1ZmZlcmVkfScsCiAgIHByb3NyYyA9PiAncGdfZ2V0X2Fpb3MnIH0sCiAK
K3sgb2lkID0+ICc5MjAwJywKKyAgZGVzY3IgPT4gJ3Rlc3QgaWYgdHdvIHZhbHVlcyBoYXZlIHRo
ZSBzYW1lIGJpbmFyeSByZXByZXNlbnRhdGlvbicsCisgIHByb25hbWUgPT4gJ3BnX2RhdHVtX2lt
YWdlX2VxdWFsJywgcHJvaXNzdHJpY3QgPT4gJ2YnLAorICBwcm9sZWFrcHJvb2YgPT4gJ3QnLCBw
cm9yZXR0eXBlID0+ICdib29sJywKKyAgcHJvYXJndHlwZXMgPT4gJ2FueWVsZW1lbnQgYW55ZWxl
bWVudCcsCisgIHByb3NyYyA9PiAncGdfZGF0dW1faW1hZ2VfZXF1YWwnIH0sCisKIF0KZGlmZiAt
LWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvb3ByX3Nhbml0eS5vdXQgYi9zcmMvdGVz
dC9yZWdyZXNzL2V4cGVjdGVkL29wcl9zYW5pdHkub3V0CmluZGV4IGEzNTdlMWQwYzBlLi41N2Zl
OGQ2ZWRlOCAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9vcHJfc2FuaXR5
Lm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL29wcl9zYW5pdHkub3V0CkBAIC04
ODAsNiArODgwLDcgQEAgYnl0ZWEoaW50ZWdlcikKIGJ5dGVhKGJpZ2ludCkKIGJ5dGVhX2xhcmdl
cihieXRlYSxieXRlYSkKIGJ5dGVhX3NtYWxsZXIoYnl0ZWEsYnl0ZWEpCitwZ19kYXR1bV9pbWFn
ZV9lcXVhbChhbnllbGVtZW50LGFueWVsZW1lbnQpCiAtLSBDaGVjayB0aGF0IGZ1bmN0aW9ucyB3
aXRob3V0IGFyZ3VtZW50IGFyZSBub3QgbWFya2VkIGFzIGxlYWtwcm9vZi4KIFNFTEVDVCBwMS5v
aWQ6OnJlZ3Byb2NlZHVyZQogRlJPTSBwZ19wcm9jIHAxIEpPSU4gcGdfbmFtZXNwYWNlIHBuCi0t
IAoyLjUwLjEgKEFwcGxlIEdpdC0xNTUpCgo=
--000000000000d9494c06459c9a13--