[BUG] The PostgreSQL object surface builds its rows with json_*, so no column can be read on RisingWave

[cevheri]

The PostgreSQL provider builds every object row with json_agg(), json_build_object() and '[]'::json. RisingWave has no json type at all, only jsonb, so every one of those statements fails to bind and no column of any object can be read there. The object tree lists the engine's tables and materialized views, and expanding one shows none of their columns.

The engine names the remedy in its own error text.

Measured

Live RisingWave 3.0.4, single node, risingwavelabs/risingwave:latest, advertising PostgreSQL 13.14.0-RisingWave-3.0.4. Measured 2026-09-22.

Every json form is refused and every jsonb form answers:

Expression	Result
SELECT '[]'::json	refused
SELECT CAST(NULL AS json)	refused
SELECT json_agg(x) FROM (SELECT 1 x) t	refused
SELECT json_build_object('a',1)	refused
SELECT '[]'::jsonb	[]
SELECT CAST(NULL AS jsonb)	empty
SELECT jsonb_agg(x) FROM (SELECT 1 x) t	[1]
SELECT jsonb_build_object('a',1)	{"a": 1}

The error is not a generic parse failure. RisingWave suggests the fix:

function json_build_object(character varying, ...) does not exist, do you mean jsonb_build_object

A working cast does not prove a working read, so the real statement was run too, not just the primitives. The described_columns CTE from bulkDetailSql() was run verbatim against a seeded table, both ways:

-- with json_agg / json_build_object
ERROR:  Failed to bind expression: json_agg(json_build_object('name', a.attname, ...))

-- with jsonb_agg / jsonb_build_object
 relname |                                   columns
---------+------------------------------------------------------------------
 probe_t | [{"name": "id", "type": "integer", ...}, {"name": "name", ...}, {"name": "qty", ...}]

So there is no second blocker behind the first: format_type(), pg_get_expr(), pg_attrdef and the ordered aggregate all work on this engine. The type name is the whole of it.

Our own record states the wrong cause, and that is part of this bug

src/lib/db/compatibility.ts currently says of RisingWave that "the json gap is the engine's and remains", and the RisingWave row in docs/providers/README.md says the same. The measurement above refutes that: the engine has the capability under a different name, and the statement is ours. Both records must be corrected by this fix, whether or not the swap itself is made, because a wrong attribution is what stopped anyone looking at it.

It also blocked an external submission. risingwavelabs/risingwave-docs was on our listing queue and cannot be written while the released product cannot show a column on that engine.

Where it is

src/lib/db/providers/sql/postgres.ts. Grep for json_agg, json_build_object, '[]'::json and NULL::json. They appear in:

• OBJECT_DETAIL_SQL, the single-object read
• bulkDetailSql(), the bulk read, including its described_columns CTE
• CTE_FK_INFO, the foreign-key aggregate
• CTE_INDEX_INFO, the index aggregate

Why this is not just a find and replace

These statements run against every PostgreSQL-wire engine this repo measures, not only RisingWave, so the change is only safe once it has been run against them. PostgreSQL has accepted jsonb_agg and jsonb_build_object for many major versions and returns them through pg as parsed objects exactly as it does json, so the expected outcome is no visible change anywhere except RisingWave. That is the expectation to verify, not to assume.

Two differences between the types are real and should be reasoned about before the sweep: jsonb does not preserve object key order, and it drops duplicate keys. Neither applies here, because every object built by these statements has a fixed set of distinct keys and every consumer parses the result rather than reading its text. Say so in the pull request rather than leaving it unaddressed. Array element order inside jsonb_agg(... ORDER BY ...) is preserved and the column order the object browser shows depends on it, so that one is worth an assertion.

Materialize is the interesting control: docs/providers/README.md records that only the jsonb_ forms exist there and that the '[]'::json cast was nonetheless measured working, so it should be unaffected or better.

Done when

1. Expanding a RisingWave table in the object browser shows its columns, with their types, through both describeObject() and describeObjects(), measured against a live RisingWave and not against a mock.
2. The same read is unchanged on PostgreSQL, and the proof is a live run rather than an argument.
3. The same read is unchanged on Materialize, CockroachDB, YugabyteDB, TimescaleDB and OrioleDB, each named in the pull request with the version it was run against. If an engine could not be run, say which and why, rather than leaving it unstated.
4. Foreign keys and indexes still read correctly wherever they read correctly today, since CTE_FK_INFO and CTE_INDEX_INFO change too.
5. The RisingWave caveat in src/lib/db/compatibility.ts and the RisingWave row in docs/providers/README.md no longer attribute this to the engine, and state what was actually measured. The tier is re-assessed against what now works.
6. Column order inside a read object is still the table's column order.

Tests, which are required

Write the failing test first. 100 percent line coverage is a required check, so tests land in the same pull request.

• tests/integration/db/postgres-provider.test.ts for both reads.
• The provider triad applies: docs/providers/postgres.md changes in the same pull request and records the measurement.

Run one file with bun tests/run-tests.ts tests/integration/db/postgres-provider.test.ts. Never bun test over a directory.

Reproducing without a cluster

docker run -d --name rw -p 14566:4566 risingwavelabs/risingwave:latest single_node
psql -h 127.0.0.1 -p 14566 -U root -d dev -c "CREATE TABLE probe_t (id int primary key, name varchar, qty int);"
psql -h 127.0.0.1 -p 14566 -U root -d dev -c "SELECT json_build_object('a',1);"
psql -h 127.0.0.1 -p 14566 -U root -d dev -c "SELECT jsonb_build_object('a',1);"

Then connect LibreDB Studio to it as a postgres connection on port 14566, user root, database dev, empty password, and expand probe_t in the object tree.

[Dharshini-RS03]

Hi, I’d like to work on this issue.

I’ll first reproduce the problem with RisingWave, then update the PostgreSQL object queries to use the supported jsonb functions and add the required integration tests. I’ll also update the RisingWave compatibility documentation and verify that the existing PostgreSQL-compatible providers are unaffected.

Does this approach look okay?