This is an automated email from the ASF dual-hosted git repository.
MuhammadTahaNaveed pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/master by this push:
new d1b7cc4e Fix agtype B-tree comparison strategy numbers (#2577)
d1b7cc4e is described below
commit d1b7cc4e1b8d708720740c5ed38ce3826f2ff2ea
Author: Eeshwar Gurushankar <[email protected]>
AuthorDate: Sat Oct 10 23:26:32 2026 -0700
Fix agtype B-tree comparison strategy numbers (#2577)
Register >= as strategy 4 and > as strategy 5, matching PostgreSQL.
The reversed mapping causes indexed >= queries to omit boundary rows.
Repair existing operator-family entries during upgrade without dropping
dependent indexes. Update the existing pg_opclass row to invalidate
cached ordering operators in sessions that used agtype before upgrade.
Cover indexed boundaries, duplicate values, forward/backward scans,
and existing indexes and ordering caches across upgrade. Correct the
existing >= expectation that omitted its boundary row.
Verified on PostgreSQL 18.4: build with COPT=-Werror and all 43 core
regression tests passed.
---
age--1.8.0--y.y.y.sql | 35 ++++++++++++++
regress/expected/age_upgrade.out | 38 +++++++++++++++
regress/expected/index.out | 101 +++++++++++++++++++++++++++++++++++++--
regress/sql/age_upgrade.sql | 15 ++++++
regress/sql/index.sql | 36 ++++++++++++++
sql/agtype_comparison.sql | 4 +-
6 files changed, 224 insertions(+), 5 deletions(-)
diff --git a/age--1.8.0--y.y.y.sql b/age--1.8.0--y.y.y.sql
index 097d066e..974604dc 100644
--- a/age--1.8.0--y.y.y.sql
+++ b/age--1.8.0--y.y.y.sql
@@ -65,3 +65,38 @@ CREATE FUNCTION ag_catalog.load_edges_from_file(graph_name
name,
RETURNS void
LANGUAGE c
AS 'MODULE_PATHNAME';
+
+DO $$
+DECLARE
+ btree_family oid;
+BEGIN
+ SELECT opcfamily INTO STRICT btree_family
+ FROM pg_catalog.pg_opclass
+ WHERE opcnamespace = 'ag_catalog'::regnamespace
+ AND opcname = 'agtype_ops_btree'
+ AND opcmethod = (SELECT oid FROM pg_catalog.pg_am WHERE amname =
'btree');
+
+ UPDATE pg_catalog.pg_amop
+ SET amopstrategy = amopstrategy + 10
+ WHERE amopfamily = btree_family
+ AND amoplefttype = 'ag_catalog.agtype'::regtype
+ AND amoprighttype = 'ag_catalog.agtype'::regtype
+ AND amopstrategy IN (4, 5);
+
+ UPDATE pg_catalog.pg_amop
+ SET amopstrategy = CASE amopopr
+ WHEN 'ag_catalog.>=(ag_catalog.agtype,ag_catalog.agtype)'::regoperator
THEN 4
+ WHEN 'ag_catalog.>(ag_catalog.agtype,ag_catalog.agtype)'::regoperator
THEN 5
+ END
+ WHERE amopfamily = btree_family
+ AND amoplefttype = 'ag_catalog.agtype'::regtype
+ AND amoprighttype = 'ag_catalog.agtype'::regtype
+ AND amopstrategy IN (14, 15);
+
+ UPDATE pg_catalog.pg_opclass
+ SET opcfamily = btree_family
+ WHERE opcnamespace = 'ag_catalog'::regnamespace
+ AND opcname = 'agtype_ops_btree'
+ AND opcfamily = btree_family;
+END
+$$;
diff --git a/regress/expected/age_upgrade.out b/regress/expected/age_upgrade.out
index 446e0eb7..9c80fbdd 100644
--- a/regress/expected/age_upgrade.out
+++ b/regress/expected/age_upgrade.out
@@ -225,6 +225,19 @@ BEGIN
END;
$$;
RESET check_function_bodies;
+BEGIN;
+CREATE TEMP TABLE agtype_upgrade_test (value agtype);
+INSERT INTO agtype_upgrade_test VALUES ('1'), ('2'), ('2'), ('3');
+CREATE INDEX agtype_upgrade_test_idx ON agtype_upgrade_test USING btree
(value);
+SELECT value FROM agtype_upgrade_test ORDER BY value DESC;
+ value
+-------
+ 3
+ 2
+ 2
+ 1
+(4 rows)
+
-- Step 11: Upgrade to the current (default) version via the stamped template.
DO $$
DECLARE curr_ver text;
@@ -246,6 +259,31 @@ FROM pg_available_extensions WHERE name = 'age';
t
(1 row)
+SET LOCAL enable_seqscan = off;
+SET LOCAL enable_bitmapscan = off;
+SELECT count(*) AS greater FROM agtype_upgrade_test WHERE value > '2'::agtype;
+ greater
+---------
+ 1
+(1 row)
+
+SELECT count(*) AS greater_equal FROM agtype_upgrade_test WHERE value >=
'2'::agtype;
+ greater_equal
+---------------
+ 3
+(1 row)
+
+SELECT value FROM agtype_upgrade_test ORDER BY value DESC;
+ value
+-------
+ 3
+ 2
+ 2
+ 1
+(4 rows)
+
+DROP TABLE agtype_upgrade_test;
+COMMIT;
-- =====================================================================
-- UPGRADED INSTALL SNAPSHOTS (Steps 13-18b)
-- Capture the catalog state after upgrade from initial to current.
diff --git a/regress/expected/index.out b/regress/expected/index.out
index ec62bf57..ed7363dd 100644
--- a/regress/expected/index.out
+++ b/regress/expected/index.out
@@ -574,11 +574,12 @@ SELECT * FROM cypher('cypher_index', $$
RETURN a.name
ORDER BY a.city_id
$$) as (name agtype);
- name
--------------
+ name
+---------------
+ "Mexico City"
"Monterrey"
"Tijuana"
-(2 rows)
+(3 rows)
-- Create expression index on west_coast boolean property
CREATE INDEX city_west_coast_idx ON cypher_index."City"
@@ -754,6 +755,100 @@ DROP INDEX cypher_index.city_id_idx;
DROP INDEX cypher_index.city_west_coast_idx;
DROP INDEX cypher_index.country_life_exp_idx;
--
+-- Section 5: Agtype B-tree range boundaries
+--
+BEGIN;
+SET LOCAL enable_seqscan = off;
+SET LOCAL enable_bitmapscan = off;
+SET LOCAL enable_indexonlyscan = off;
+CREATE TEMP TABLE agtype_btree_test (value agtype);
+INSERT INTO agtype_btree_test VALUES ('1'), ('2'), ('2'), ('3');
+CREATE INDEX agtype_btree_test_idx ON agtype_btree_test USING btree (value);
+ANALYZE agtype_btree_test;
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value;
+ QUERY PLAN
+-------------------------------------------------------------
+ Index Scan using agtype_btree_test_idx on agtype_btree_test
+ Index Cond: (value > '2'::agtype)
+(2 rows)
+
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value;
+ value
+-------
+ 3
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value;
+ QUERY PLAN
+-------------------------------------------------------------
+ Index Scan using agtype_btree_test_idx on agtype_btree_test
+ Index Cond: (value >= '2'::agtype)
+(2 rows)
+
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value;
+ value
+-------
+ 2
+ 2
+ 3
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value
DESC;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Index Scan Backward using agtype_btree_test_idx on agtype_btree_test
+ Index Cond: (value > '2'::agtype)
+(2 rows)
+
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value
DESC;
+ value
+-------
+ 3
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value
DESC;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Index Scan Backward using agtype_btree_test_idx on agtype_btree_test
+ Index Cond: (value >= '2'::agtype)
+(2 rows)
+
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value
DESC;
+ value
+-------
+ 3
+ 2
+ 2
+(3 rows)
+
+SELECT value FROM agtype_btree_test WHERE value < '2'::agtype ORDER BY value;
+ value
+-------
+ 1
+(1 row)
+
+SELECT value FROM agtype_btree_test WHERE value <= '2'::agtype ORDER BY value;
+ value
+-------
+ 1
+ 2
+ 2
+(3 rows)
+
+SELECT value FROM agtype_btree_test WHERE value = '2'::agtype ORDER BY value;
+ value
+-------
+ 2
+ 2
+(2 rows)
+
+DROP TABLE agtype_btree_test;
+COMMIT;
+--
-- General Cleanup
--
SELECT drop_graph('cypher_index', true);
diff --git a/regress/sql/age_upgrade.sql b/regress/sql/age_upgrade.sql
index 98ce3e21..5febc0b3 100644
--- a/regress/sql/age_upgrade.sql
+++ b/regress/sql/age_upgrade.sql
@@ -236,6 +236,13 @@ END;
$$;
RESET check_function_bodies;
+BEGIN;
+CREATE TEMP TABLE agtype_upgrade_test (value agtype);
+INSERT INTO agtype_upgrade_test VALUES ('1'), ('2'), ('2'), ('3');
+CREATE INDEX agtype_upgrade_test_idx ON agtype_upgrade_test USING btree
(value);
+
+SELECT value FROM agtype_upgrade_test ORDER BY value DESC;
+
-- Step 11: Upgrade to the current (default) version via the stamped template.
DO $$
DECLARE curr_ver text;
@@ -254,6 +261,14 @@ $$;
SELECT installed_version = default_version AS upgraded_to_current
FROM pg_available_extensions WHERE name = 'age';
+SET LOCAL enable_seqscan = off;
+SET LOCAL enable_bitmapscan = off;
+SELECT count(*) AS greater FROM agtype_upgrade_test WHERE value > '2'::agtype;
+SELECT count(*) AS greater_equal FROM agtype_upgrade_test WHERE value >=
'2'::agtype;
+SELECT value FROM agtype_upgrade_test ORDER BY value DESC;
+DROP TABLE agtype_upgrade_test;
+COMMIT;
+
-- =====================================================================
-- UPGRADED INSTALL SNAPSHOTS (Steps 13-18b)
-- Capture the catalog state after upgrade from initial to current.
diff --git a/regress/sql/index.sql b/regress/sql/index.sql
index d4a4b24a..1dd475d3 100644
--- a/regress/sql/index.sql
+++ b/regress/sql/index.sql
@@ -432,6 +432,42 @@ DROP INDEX cypher_index.city_id_idx;
DROP INDEX cypher_index.city_west_coast_idx;
DROP INDEX cypher_index.country_life_exp_idx;
+--
+-- Section 5: Agtype B-tree range boundaries
+--
+BEGIN;
+SET LOCAL enable_seqscan = off;
+SET LOCAL enable_bitmapscan = off;
+SET LOCAL enable_indexonlyscan = off;
+
+CREATE TEMP TABLE agtype_btree_test (value agtype);
+INSERT INTO agtype_btree_test VALUES ('1'), ('2'), ('2'), ('3');
+CREATE INDEX agtype_btree_test_idx ON agtype_btree_test USING btree (value);
+ANALYZE agtype_btree_test;
+
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value;
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value;
+
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value;
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value;
+
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value
DESC;
+SELECT value FROM agtype_btree_test WHERE value > '2'::agtype ORDER BY value
DESC;
+
+EXPLAIN (COSTS OFF)
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value
DESC;
+SELECT value FROM agtype_btree_test WHERE value >= '2'::agtype ORDER BY value
DESC;
+
+SELECT value FROM agtype_btree_test WHERE value < '2'::agtype ORDER BY value;
+SELECT value FROM agtype_btree_test WHERE value <= '2'::agtype ORDER BY value;
+SELECT value FROM agtype_btree_test WHERE value = '2'::agtype ORDER BY value;
+
+DROP TABLE agtype_btree_test;
+COMMIT;
+
--
-- General Cleanup
--
diff --git a/sql/agtype_comparison.sql b/sql/agtype_comparison.sql
index 5b937e84..55200366 100644
--- a/sql/agtype_comparison.sql
+++ b/sql/agtype_comparison.sql
@@ -1440,8 +1440,8 @@ CREATE OPERATOR CLASS agtype_ops_btree
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
- OPERATOR 4 >,
- OPERATOR 5 >=,
+ OPERATOR 4 >=,
+ OPERATOR 5 >,
FUNCTION 1 ag_catalog.agtype_btree_cmp(agtype, agtype);
CREATE FUNCTION ag_catalog.agtype_hash_cmp(agtype)
