On Wed, 14 Oct 2026 16:25:33 +0200, Matthias van de Meent
<[email protected]> wrote:
> On Fri, 2 Oct 2026 at 01:34, Alberto Piai <[email protected]> wrote:
> >
> > 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).
>
> I've thought about it, but I couldn't think of any. Adding
> pg_datum_image_equal actually means that users won't have to go
> through expensive record (de)construction for binary equality, which
> would be a very useful performance improvement.
>
> > 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.
>
> What do you mean by "more updates than expected"?
>
> > 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 don't think this is useful. The distinction is already made
> sufficiently clear in the added docs, and because the function doesn't
> map to indexable operators there's little chance people will
> accidentally use this when they actually want their type's = operator.
>
>
> Kind regards,
>
> Matthias van de Meent
> Databricks (https://www.databricks.com)
Hello Matthias,
This thread caught my eye as I was working on differential selectivity
testing, so I took a look at it. I was able to validate the function
you propose against its intended contract and have a small regression
test that you might want to add, your call.
The contract I checked: for non-NULL values of the same type,
pg_datum_image_equal(a, b) is true iff the type's send() output for a
and b is identical, with NULL vs NULL true and NULL vs non-NULL false.
That is the line between image equality and = that the function exists
to draw, so the test covers the three cases where they diverge:
value-equal, image-unequal: float8 0.0 vs -0.0 ; numeric 1.0 vs 1.00
=, and image-equal: float8 'NaN' vs 'NaN' (float = treats NaN as
equal, so = and the image agree here)
plus a bool_and over int4/int8/text/bool/numeric/float8 that the
function agrees with <type>send() equality on every value pair.
Against v5 (1b91f546b62) every case holds. One wrinkle worth a comment
in the test: a -0.0 literal is folded to +0.0 before it reaches the
function, so to get a genuine negative zero on disk the test routes
values through a table and computes (-1.0::float8) * 0.0; comparing that
to +0.0 correctly reports image-unequal.
Patch attached, as a companion to apply on top of your branch
(src/test/regress/sql/datum_image_equal.sql, added to the misc_functions
parallel group). Fold it in or treat it as a starting point, whichever
you prefer.
best.
-greg
From 709a929b0245603c19a21c411154d84f49dfcf09 Mon Sep 17 00:00:00 2001
From: Greg Burd <[email protected]>
Date: Mon, 26 Oct 2026 11:12:34 +0000
Subject: [PATCH] Add regression test pinning the pg_datum_image_equal contract
---
.../regress/expected/datum_image_equal.out | 108 ++++++++++++++++++
src/test/regress/parallel_schedule | 3 +-
src/test/regress/sql/datum_image_equal.sql | 80 +++++++++++++
3 files changed, 190 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/datum_image_equal.out
create mode 100644 src/test/regress/sql/datum_image_equal.sql
diff --git a/src/test/regress/expected/datum_image_equal.out
b/src/test/regress/expected/datum_image_equal.out
new file mode 100644
index 00000000000..3b5ffe3ae94
--- /dev/null
+++ b/src/test/regress/expected/datum_image_equal.out
@@ -0,0 +1,108 @@
+--
+-- Contract for pg_datum_image_equal: it reports whether two values of the same
+-- type have identical on-disk binary images -- NOT whether they are equal by
+-- the type's = operator. The two differ precisely at the cases this function
+-- exists to serve (REFRESH MATERIALIZED VIEW CONCURRENTLY, incremental sort):
+--
+-- value-equal but image-UNequal: float8 0.0 vs -0.0 (sign bit)
+-- numeric 1.0 vs 1.00 (stored scale)
+-- value-UNequal (by IEEE) but = float8 'NaN' vs 'NaN' -- and image-equal
+-- (PostgreSQL's float = treats NaN as equal to NaN, unlike IEEE, so here
+-- both = and the image agree; the point is the function tracks the image).
+--
+-- and NULL handling: NULL vs NULL is equal, NULL vs non-NULL is not.
+--
+-- The oracle is the type's own binary output: two non-NULL values have equal
+-- images iff their <type>send() bytes are identical. The test checks that
+-- equivalence across a range of types and the known edge cases, so a future
+-- change that accidentally made the function track = instead of the image is
+-- caught.
+--
+-- Literals are folded, so -0.0 and a computed -0.0 are different data: route
+-- values through a table to get the genuine datums on disk.
+CREATE TABLE die_f8 (x float8);
+INSERT INTO die_f8 VALUES (0.0), ((-1.0::float8) * 0.0), ('NaN'),
('Infinity'), (1.5);
+-- 0.0 and -0.0 are = but have different images.
+SELECT 'f8 0 vs -0' AS t,
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) LIMIT 1) =
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) DESC LIMIT
1) AS by_eq,
+ pg_datum_image_equal(
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) LIMIT 1),
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) DESC LIMIT
1)) AS by_image;
+ t | by_eq | by_image
+------------+-------+----------
+ f8 0 vs -0 | t | f
+(1 row)
+
+-- NaN is not = to NaN, but two NaNs have the same image.
+SELECT 'f8 NaN vs NaN' AS t,
+ ('NaN'::float8 = 'NaN'::float8) AS by_eq,
+ pg_datum_image_equal('NaN'::float8, 'NaN'::float8) AS by_image;
+ t | by_eq | by_image
+---------------+-------+----------
+ f8 NaN vs NaN | t | t
+(1 row)
+
+-- numeric 1.0 and 1.00 are = but stored with different scale -> different
image.
+SELECT 'numeric 1.0 vs 1.00' AS t,
+ (1.0::numeric = 1.00::numeric) AS by_eq,
+ pg_datum_image_equal(1.0::numeric, 1.00::numeric) AS by_image;
+ t | by_eq | by_image
+---------------------+-------+----------
+ numeric 1.0 vs 1.00 | t | f
+(1 row)
+
+-- NULL handling.
+SELECT 'null vs null' AS t, pg_datum_image_equal(NULL::int, NULL::int) AS
by_image;
+ t | by_image
+--------------+----------
+ null vs null | t
+(1 row)
+
+SELECT 'null vs value' AS t, pg_datum_image_equal(NULL::int, 1) AS by_image;
+ t | by_image
+---------------+----------
+ null vs value | f
+(1 row)
+
+-- General equivalence: for non-NULL same-type values, image equality agrees
+-- with identical binary output, across by-value and varlena types. Every row
+-- must report t.
+SELECT format('%s image==send agrees', typ) AS t, bool_and(ok) AS all_true
+FROM (
+ SELECT 'int4' typ,
+ pg_datum_image_equal(a::int4, b::int4) = (int4send(a::int4) =
int4send(b::int4)) ok
+ FROM (VALUES (0,0),(1,1),(1,2),(-1,-1),(2147483647,-2147483648)) v(a,b)
+ UNION ALL
+ SELECT 'int8',
+ pg_datum_image_equal(a::int8, b::int8) = (int8send(a::int8) =
int8send(b::int8))
+ FROM (VALUES (0,0),(1,2),(9223372036854775807,-1)) v(a,b)
+ UNION ALL
+ SELECT 'text',
+ pg_datum_image_equal(a::text, b::text) = (textsend(a::text) =
textsend(b::text))
+ FROM (VALUES ('a','a'),('a','b'),('','a'),('abc','abc')) v(a,b)
+ UNION ALL
+ SELECT 'bool',
+ pg_datum_image_equal(a::bool, b::bool) = (boolsend(a::bool) =
boolsend(b::bool))
+ FROM (VALUES (true,true),(true,false)) v(a,b)
+ UNION ALL
+ SELECT 'numeric',
+ pg_datum_image_equal(a::numeric, b::numeric) =
(numeric_send(a::numeric) = numeric_send(b::numeric))
+ FROM (VALUES ('1.0','1.00'),('0','0.0'),('100','1e2'),('NaN','NaN')) v(a,b)
+ UNION ALL
+ SELECT 'float8',
+ pg_datum_image_equal(a::float8, b::float8) = (float8send(a::float8) =
float8send(b::float8))
+ FROM (VALUES ('1.5','1.5'),('NaN','NaN'),('Infinity','-Infinity')) v(a,b)
+) s
+GROUP BY typ ORDER BY typ;
+ t | all_true
+----------------------------+----------
+ bool image==send agrees | t
+ float8 image==send agrees | t
+ int4 image==send agrees | t
+ int8 image==send agrees | t
+ numeric image==send agrees | t
+ text image==send agrees | t
+(6 rows)
+
+DROP TABLE die_f8;
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index 75063f87a4a..303a8545b7a 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -76,7 +76,8 @@ test: brin_bloom brin_multi
# ----------
# Another group of parallel tests
# ----------
-test: create_table_like alter_generic alter_operator misc async dbsize merge
misc_functions nls sysviews tsrf tid tidscan tidrangescan collate.utf8
collate.icu.utf8 incremental_sort create_role without_overlaps generated_virtual
+test: create_table_like alter_generic alter_operator misc async dbsize merge
misc_functions
+test: datum_image_equal nls sysviews tsrf tid tidscan tidrangescan
collate.utf8 collate.icu.utf8 incremental_sort create_role without_overlaps
generated_virtual
# collate.linux.utf8 and collate.icu.utf8 tests cannot be run in parallel with
each other
# psql depends on create_am
diff --git a/src/test/regress/sql/datum_image_equal.sql
b/src/test/regress/sql/datum_image_equal.sql
new file mode 100644
index 00000000000..27a06906c3d
--- /dev/null
+++ b/src/test/regress/sql/datum_image_equal.sql
@@ -0,0 +1,80 @@
+--
+-- Contract for pg_datum_image_equal: it reports whether two values of the same
+-- type have identical on-disk binary images -- NOT whether they are equal by
+-- the type's = operator. The two differ precisely at the cases this function
+-- exists to serve (REFRESH MATERIALIZED VIEW CONCURRENTLY, incremental sort):
+--
+-- value-equal but image-UNequal: float8 0.0 vs -0.0 (sign bit)
+-- numeric 1.0 vs 1.00 (stored scale)
+-- value-UNequal (by IEEE) but = float8 'NaN' vs 'NaN' -- and image-equal
+-- (PostgreSQL's float = treats NaN as equal to NaN, unlike IEEE, so here
+-- both = and the image agree; the point is the function tracks the image).
+--
+-- and NULL handling: NULL vs NULL is equal, NULL vs non-NULL is not.
+--
+-- The oracle is the type's own binary output: two non-NULL values have equal
+-- images iff their <type>send() bytes are identical. The test checks that
+-- equivalence across a range of types and the known edge cases, so a future
+-- change that accidentally made the function track = instead of the image is
+-- caught.
+--
+
+-- Literals are folded, so -0.0 and a computed -0.0 are different data: route
+-- values through a table to get the genuine datums on disk.
+CREATE TABLE die_f8 (x float8);
+INSERT INTO die_f8 VALUES (0.0), ((-1.0::float8) * 0.0), ('NaN'),
('Infinity'), (1.5);
+
+-- 0.0 and -0.0 are = but have different images.
+SELECT 'f8 0 vs -0' AS t,
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) LIMIT 1) =
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) DESC LIMIT
1) AS by_eq,
+ pg_datum_image_equal(
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) LIMIT 1),
+ (SELECT x FROM die_f8 WHERE x = 0.0 ORDER BY float8send(x) DESC LIMIT
1)) AS by_image;
+
+-- NaN is not = to NaN, but two NaNs have the same image.
+SELECT 'f8 NaN vs NaN' AS t,
+ ('NaN'::float8 = 'NaN'::float8) AS by_eq,
+ pg_datum_image_equal('NaN'::float8, 'NaN'::float8) AS by_image;
+
+-- numeric 1.0 and 1.00 are = but stored with different scale -> different
image.
+SELECT 'numeric 1.0 vs 1.00' AS t,
+ (1.0::numeric = 1.00::numeric) AS by_eq,
+ pg_datum_image_equal(1.0::numeric, 1.00::numeric) AS by_image;
+
+-- NULL handling.
+SELECT 'null vs null' AS t, pg_datum_image_equal(NULL::int, NULL::int) AS
by_image;
+SELECT 'null vs value' AS t, pg_datum_image_equal(NULL::int, 1) AS by_image;
+
+-- General equivalence: for non-NULL same-type values, image equality agrees
+-- with identical binary output, across by-value and varlena types. Every row
+-- must report t.
+SELECT format('%s image==send agrees', typ) AS t, bool_and(ok) AS all_true
+FROM (
+ SELECT 'int4' typ,
+ pg_datum_image_equal(a::int4, b::int4) = (int4send(a::int4) =
int4send(b::int4)) ok
+ FROM (VALUES (0,0),(1,1),(1,2),(-1,-1),(2147483647,-2147483648)) v(a,b)
+ UNION ALL
+ SELECT 'int8',
+ pg_datum_image_equal(a::int8, b::int8) = (int8send(a::int8) =
int8send(b::int8))
+ FROM (VALUES (0,0),(1,2),(9223372036854775807,-1)) v(a,b)
+ UNION ALL
+ SELECT 'text',
+ pg_datum_image_equal(a::text, b::text) = (textsend(a::text) =
textsend(b::text))
+ FROM (VALUES ('a','a'),('a','b'),('','a'),('abc','abc')) v(a,b)
+ UNION ALL
+ SELECT 'bool',
+ pg_datum_image_equal(a::bool, b::bool) = (boolsend(a::bool) =
boolsend(b::bool))
+ FROM (VALUES (true,true),(true,false)) v(a,b)
+ UNION ALL
+ SELECT 'numeric',
+ pg_datum_image_equal(a::numeric, b::numeric) =
(numeric_send(a::numeric) = numeric_send(b::numeric))
+ FROM (VALUES ('1.0','1.00'),('0','0.0'),('100','1e2'),('NaN','NaN')) v(a,b)
+ UNION ALL
+ SELECT 'float8',
+ pg_datum_image_equal(a::float8, b::float8) = (float8send(a::float8) =
float8send(b::float8))
+ FROM (VALUES ('1.5','1.5'),('NaN','NaN'),('Infinity','-Infinity')) v(a,b)
+) s
+GROUP BY typ ORDER BY typ;
+
+DROP TABLE die_f8;
--
2.47.3
