Hoi Tom, pgsql-bugs,
Now it's morning I've thought of a way to reproduce it more easily, see the attached script. The tricky part is getting the tuples in a position that triggers the planner in the right way. So the script includes a list of (ctid, primary key) and creates a table using that with quite a large amount of bloat. It then creates some constraints, vacuums and runs the offending query. On my system it reproduces with 100% reliability (so far anyway).
10:41 $ PGPASSWORD=pass psql -h 127.0.0.1 -U postgres db2 < /tmp/reproduce2.sql
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
DROP TABLE
DROP TABLE
DROP TABLE
NOTICE: table "input" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
COPY 1438
INSERT 0 166
ALTER TABLE
SELECT 192000
SELECT 192000
DELETE 1356
ALTER TABLE
DELETE 189206
VACUUM
VACUUM
ERROR: set-valued function called in context that cannot accept a set
LINE 2: unnest(ARRAY[]::jsonb[]) ->> 'timestamp' AS timestam...
^
Hope this helps,
Martijn
PG Bug reporting form <noreply@postgresql.org> writes:
> db=# explain SELECT generate_subscripts(ARRAY[]::integer[], 1) AS id,
> unnest(ARRAY[]::jsonb[]) ->> 'timestamp' AS timestamp
> FROM results
> JOIN groups ON groups.id = results.group_id
> WHERE results.search_id = 3336
> order by timestamp;
> The error is:
> ERROR: set-valued function called in context that cannot accept a set
> LINE 2: unnest(ARRAY[]::jsonb[]) ->> 'timestamp' AS timestam...
Hmm, that certainly seems like a bug, but I fear it's impossible
to investigate without a reproducible test case.
regards, tom lane
--
