jok-carbonarc commented on issue #2587: URL: https://github.com/apache/age/issues/2587#issuecomment-6239064753
The cause is the strategy numbers in `agtype_ops_btree`. It registers `OPERATOR 4 >` and `OPERATOR 5 >=`, but btree reads strategy 4 as `>=` and 5 as `>` ([stratnum.h](https://github.com/postgres/postgres/blob/REL_18_STABLE/src/include/access/stratnum.h)). `_bt_first` positions a forward scan from the strategy number, so a `>=` bound (and a commuted `v <= prop`) starts after the keys equal to it, as if it were `>`. `_bt_checkkeys` then filters with the operator's own function. That is why `>` is right (it starts at the equal keys, then drops them), why backward scans are right, and why `a >= 5 AND a <= 5` returns nothing. It is fixed on master by #2577 (merged 2026-10-11, d1b7cc4e1b8d708720740c5ed38ce3826f2ff2ea), after this issue was opened, which would explain `dev_snapshot_master` still showing it. Every release branch, PG11 to PG18, still has the old mapping, so the `release_PG18_1.8.0`, `release_PG17_1.7.0` and `release_PG16_1.6.0` images in the table are affected until #2577 is backported: ``` OPERATOR 4 >, OPERATOR 5 >=, ``` An existing install can be corrected in place, with no reindex: the index order comes from support function 1, `agtype_btree_cmp`, which does not change. We ran this as superuser on a 1.8.0 / PostgreSQL 18.6 database with about 3,300 agtype btree indexes. Afterwards the indexed `>=` forms (commuted, string values, a Cypher parameter) matched the sequential scan, and the change replicated to the standby. In psql: ```sql BEGIN; SELECT oid AS fam FROM pg_opfamily WHERE opfname = 'agtype_ops_btree' AND opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') \gset UPDATE pg_amop SET amopstrategy = 6 WHERE amopfamily = :fam AND amopstrategy = 4; UPDATE pg_amop SET amopstrategy = 4 WHERE amopfamily = :fam AND amopstrategy = 5; UPDATE pg_amop SET amopstrategy = 5 WHERE amopfamily = :fam AND amopstrategy = 6; -- expect 1 <, 2 <=, 3 =, 4 >=, 5 > SELECT amopstrategy, amopopr::regoperator FROM pg_amop WHERE amopfamily = :fam ORDER BY 1; COMMIT; ``` Run it only while strategy 4 is still `>`; a second run swaps them back. One caveat, which #2577's upgrade script also handles: a session that was open before the swap keeps the old "greater than" operator in its type cache, and an `ORDER BY ... DESC` on agtype in that session then fails with `operator NNNNN is not a valid ordering operator`. `DISCARD ALL` does not clear it; reconnecting does. #2577 touches the `pg_opclass` row so the cache is invalidated instead. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
