GIN jsonpath scans miss rows when methods follow a predicate
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254188psql -h localhost -U postgresBuilt from patchset v3 (message #3), October 22, 2026 at 09:10 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254188_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254188_3 && git checkout t254188_3Patchset v3 (message #3) is on t254188_3
Hi,
Codex reported me an issue where GIN scans can miss matching rows when
methods are applied to a jsonpath predicate's result. For example:
------------------------------------------
CREATE TEMP TABLE t AS SELECT '2'::jsonb AS j;
-- Returns one row.
SELECT * FROM t WHERE j @@ '($ == 1).size().boolean()';
CREATE INDEX ON t USING gin (j);
SET enable_seqscan = off;
-- Returns no rows, but should return one.
SELECT * FROM t WHERE j @@ '($ == 1).size().boolean()';
RESET enable_seqscan;
------------------------------------------
In the default lax mode, "($ == 1).size().boolean()" evaluates to true.
But, extract_jsp_bool_expr() ignores the following methods and
incorrectly extracts an index condition from "$ == 1", causing the issue.
The attached patch fixes this by skipping index condition extraction for
predicates with following items.
Regards,
--
Fujii Masao
Fujii Masao <masao.fujii@gmail.com> writes:
Codex reported me an issue where GIN scans can miss matching rows when
methods are applied to a jsonpath predicate's result.
SELECT * FROM t WHERE j @@ '($ == 1).size().boolean()';
This is related to one of the bugs I fixed in b28a2b2bf, but I think
it's different. I fear your patch might be too sweeping though ---
are you sure it doesn't reject cases that would be valid? I'd prefer
to inject the no-nextPos test on a per-node-type basis.
regards, tom lane
On Wed, Oct 14, 2026 at 10:25 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
This is related to one of the bugs I fixed in b28a2b2bf, but I think
it's different. I fear your patch might be too sweeping though ---
are you sure it doesn't reject cases that would be valid? I'd prefer
to inject the no-nextPos test on a per-node-type basis.
Yes, you're right. Seems we can safely extract an index condition if
the combined effect of the items following a predicate cannot turn false
or unknown predicate result into true. For example,
"($ == 1).string().lower().boolean()" can still use its index condition.
So I've updated the patch to check the entire chain of items following
a predicate. It allows chains that are safe as a whole and conservatively
skips extraction otherwise.
I've identified 13 such item types so far, but of course I may have
missed some. Thoughts?
Regards,
--
Fujii Masao