[PG19] jsonb_object_agg as a window function returns stale values
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.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
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:t254167psql -h localhost -U postgresBuilt from patchset v2 (message #2), October 14, 2026 at 04:15 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 t254167_2 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 t254167_2 && git checkout t254167_2Patchset v2 (message #2) is on t254167_2
Hi,
This is High 2 from my list in [1]/messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com.
Since b61aa76e458, jsonb_object_agg as a window function can return
an old value for a key.
select g, jsonb_object_agg(k, v) over (order by g)
from (values (1,'a',1),(1,'a',2),(1,'a',3),(1,'b',5),(2,'b',6),
(3,'a',7)) t(g,k,v);
For g=2 master gives {"a": 3, "b": 5}. 18 gives {"a": 3, "b": 6}.
The second query in 0002 runs over 120 rows with 8 keys, and 97 rows
come back wrong.
In a window the final function runs once per row. Each run ends the
object on the shared state, which sorts the pairs array and removes
duplicate keys in place. A pair added after that can lose to an older
pair with the same key. The comment there assumes this step can run
many times on the same state, and that does not hold.
0001 makes the final function work on a copy of the pairs array. The
cost is one memcpy per final call. 0002 adds a test. jsonb_agg is not
affected. The patches apply to master and REL_19_STABLE.
The commit came from [2]/messages/by-id/1060917.1753202222@sss.pgh.pa.us.
A smaller fix is to renumber the pairs after the dedup. That fixes
the queries above. But the same step drops null pairs from the state,
so this still differs from 18:
select g, jsonb_object_agg_unique_strict(k, v) over (order by g)
from (values (1, 'a', null::int), (2, 'a', 1)) t(g, k, v);
18 and 0001 report a duplicate key. master and the smaller fix return
{"a": 1} for g=2.
[1]: /messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
/messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
[2]: /messages/by-id/1060917.1753202222@sss.pgh.pa.us
Thanks,
Shihao
Attachments:
t254167_1v1-0002-Add-test-for-jsonb_object_agg-as-a-window-functio.patchapplication/octet-stream; name=v1-0002-Add-test-for-jsonb_object_agg-as-a-window-functio.patchDownload+30-1
v1-0001-Fix-jsonb_object_agg-used-as-a-window-function.patchapplication/octet-stream; name=v1-0001-Fix-jsonb_object_agg-used-as-a-window-function.patchDownload+16-10
Hi Tom,
One alternative could be to use the extra palloc/memcpy only when
skip_nulls is true, after fixing uniqueifyJsonbObject to renumber
the pairs.
v2 does that, with a narrower test for the copy. Removing null pairs
from the state is harmless unless unique_keys is also set, since the
dedup loop skips nulls before it looks for duplicates. So 0001 copies
the pairs only for jsonb_object_agg_unique_strict.
With your benchmark on a non-assert build, HEAD and v2 both take about
10 ms of backend CPU, and v1 takes 5.6 s :( .
(Independently of all this, the documentation for the json-object
aggregates is seriously awful.
0003 is a try at that. It says which value wins for a repeated key,
and that null values are dropped first but still count in the unique
check.
Thanks,
Shihao
Attachments:
t254167_2v2-0002-Add-tests-for-jsonb_object_agg-as-a-window-functi.patchapplication/octet-stream; name=v2-0002-Add-tests-for-jsonb_object_agg-as-a-window-functi.patchDownload+37-1
v2-0003-Document-duplicate-key-handling-of-the-JSON-objec.patchapplication/octet-stream; name=v2-0003-Document-duplicate-key-handling-of-the-JSON-objec.patchDownload+9-3
v2-0001-Fix-jsonb_object_agg-used-as-a-window-function.patchapplication/octet-stream; name=v2-0001-Fix-jsonb_object_agg-used-as-a-window-function.patchDownload+27-4
Import Notes
Reply to msg id not found: 730101.1791910604@sss.pgh.pa.us
On Wed, Oct 14, 2026 at 12:03 AM shihao zhong <zhong950419@gmail.com> wrote:
One alternative could be to use the extra palloc/memcpy only when
skip_nulls is true, after fixing uniqueifyJsonbObject to renumber
the pairs.v2 does that, with a narrower test for the copy. Removing null pairs
from the state is harmless unless unique_keys is also set, since the
dedup loop skips nulls before it looks for duplicates. So 0001 copies
the pairs only for jsonb_object_agg_unique_strict.With your benchmark on a non-assert build, HEAD and v2 both take about
10 ms of backend CPU, and v1 takes 5.6 s :( .(Independently of all this, the documentation for the json-object
aggregates is seriously awful.0003 is a try at that. It says which value wins for a repeated key,
and that null values are dropped first but still count in the unique
check.
Should this have a PG 19 open item?
- Melanie
Melanie Plageman <melanieplageman@gmail.com> writes:
On Wed, Oct 14, 2026 at 12:03 AM shihao zhong <zhong950419@gmail.com> wrote:
0003 is a try at that. It says which value wins for a repeated key,
and that null values are dropped first but still count in the unique
check.
Should this have a PG 19 open item?
If you like, but I plan to resolve it today.
regards, tom lane