Message-ID: <bca753e5-58aa-45d2-b87b-432489bbefa5@dunslane.net>
Date: Thu, 12 Mar 2026 13:23:40 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #19431: limitation of the data type jsonb equals 8Kb
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: perovaa@sbermarketing.ru, pgsql-bugs@lists.postgresql.org
References: <19431-0a33306c00df939f@postgresql.org>
 <5e048736-26bd-4254-84fa-c74ad0bf0c88@dunslane.net>
 <4517.1773330924@sss.pgh.pa.us>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAlwQTAQgAQQIbAwIX
 gAIZAQULCQgHAwUVCgkICwUWAgMBAAIeBRYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNGd
 BQkdEO8nAAoJEJn6f8tZ/DuBq74H/jkTR4Zi3stbw+xC7v2u3QozssK7MYPL2AsVfh7OealS
 h182fiWXpfvmmAB7WUHbhk9GC2RAOnHI/2d2jgKaMLAHsGYOT0YopTVIwRY43fCw/mK67yxc
 wmDcX+zyKfLaivNbf5A7QPLNwda98bEAMSJ8Sn652Uc6cA8t3uKGsVzbRBQOoYzjgvBCfSrE
 9ql3PDNg0l4BfAqabd2f70ZUm9VAMEPrgv/v2xI7M2XiL4g5BVmqLCOwxLM8RMCotCuoweUr
 VO43DeBCIDwLxotMJKvGWDjBzQYlU1NPUAtNcz/gN9ITUe1VUGjyvGj4u1lxBOcQQUw7l1+T
 5moZ4iZxXzvOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oKWaqf+wmW4tpDe/VV
 cxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7llV0xSSoFiieQBsr
 GvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8BoVrRFcdzlIY/NFN
 hFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C4R+M6w7o2PLGOjdZ
 +kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaYL++LAEOqrlvm2Tvq
 QqZRtnEAEQEAAcLAfAQYAQgAJgIbDBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNI2BQkd
 EODdAAoJEJn6f8tZ/DuBfw0IAKTsfD40teP/pp+bsLLMSxPXUYrrprTj7WFB5v61p6dkpSr/
 qXmMlyahdxQFaPmfVgVirB1Vk/kHiWNnnGjfUV9nB2Zg9LI0Xb9/ts3LsUiRWXzG3tkMY6XL
 vsVOxW4XFRND9l2q+WW93aZ1DZl+fqWfYgMvsusFRhmGFOKTRfKPta2Pkv+AhA24N4+PrR5p
 bU4k2MO8PAGiK8eaYKGFG1bHKuAvoDoF7WXJ3FHxuWqLnKEt4dfOLm5pAe3zq1Lt6q8azT9i
 QWGpSAK5vQUWQHBHpiDjdPeqKZ6HiAXIIKfSmb+jrvXBqoP+D6/K7rUjG2aXiRtTIAXms9sm
 VRu7cmw=
In-Reply-To: <4517.1773330924@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/bca753e5-58aa-45d2-b87b-432489bbefa5%40dunslane.net>
Precedence: bulk


On 2026-03-12 Th 11:55 AM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> On 2026-03-12 Th 8:11 AM, PG Bug reporting form wrote:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference:      19431
>>> Logged by:          Alex Perov
>>> Email address:      perovaa@sbermarketing.ru
>>> PostgreSQL version: 18.3
>>> Operating system:   Windows 10
>>> Description:
>>>
>>> Hello.
>>> I have a type:
>>> CREATE TYPE public.t_dict AS
>>> (
>>> id bigint,
>>> name text,
>>> gid uuid,
>>> alias text,
>>> bgcolor text,
>>> disabled boolean,
>>> checked boolean,
>>> options jsonb,
>>> "order" integer
>>> );
>>> and function
>>> CREATE OR REPLACE FUNCTION public.f_func(data jsonb, uid bigint, context
>>> text = null, ext text = null, pid bigint = null, pgid uuid = null)
>>> returns setof rd.t_dict
>>> language plpgsql
>>> AS $$
>>> begin
>>> context := coalesce(context, data->>'context', '');
>>> ext = coalesce(ext, '');
>>>
>>> create temp table tmp_f_table of public.t_dict;
>>> if dict = 'dict1' then
>>> insert into tmp_f_table (id, name)
>>> select
>>> 1, name
>>> from public.tbl1 a;
>>> elsif dict = 'dict2' then
>>> insert into tmp_f_table (id, name, alias)
>>> select
>>> t.objecttypeid, t.objecttype, t.alias
>>> from public.tbl2 t
>>> where (t.objecttypeid <> 0)
>>> and t.active;
>>> elsif dict = 'dict3' then
>>> insert into tmp_f_table (id, name, options)
>>> select
>>> t.linktypeid, t.linktype,
>>> json_object(
>>> 'rev': t.reftypeid = (data->'objecttype')::bigint,
>>> 'items': t.options
>>> )
>>> from public.tbl3 t;
>>> end if;
>>> return query select * from tmp_f_cpdictionary;
>>> drop table tmp_f_cpdictionary;
>>> end
>>> $$;
>>> when calling a function for dict3, I have an error "could not open relation
>>> with OID 196327".
>>> As far as I know, this is due to a limitation of the data type jsonb equals
>>> 8Kb.
>>> Is it possible to fix this error without changing the logic of the code?
>>>
>> This has nothing to do with any limit on the size of jsonb, and in fact
>> jsonb can have values vastly larger than 8Kb.
>> The real problem is the well-known plan caching + temp table OID
>> mismatch issue. Here's what's actually happening:
>>     1. The function does CREATE TEMP TABLE tmp_f_table OF public.t_dict
>> at the start and DROP TABLE tmp_f_table at the end.
>>     2. On the first call, PL/pgSQL parses/plans the INSERT INTO
>> tmp_f_table and SELECT * FROM tmp_f_table statements, caching plans that
>> reference tmp_f_table by its OID (e.g., OID 196327).
>>     3. On a subsequent call, the old temp table has been dropped and a
>> new one is created with a different OID. The cached plan still
>> references the old OID leading to "could not open relation with OID 196327".
> I believe we have replanning logic that will handle that.  I think the
> actual issue is that the function tries to do this:
>
>>     return query select * from tmp_f_cpdictionary;
>>     drop table tmp_f_cpdictionary;
> The RETURN QUERY stuffed the data into a tuplestore ... but what it
> stuffed there was TOAST pointers referencing the temp table.  So the fetch
> failure occurs when we try to read out the tuplestore contents after the
> function returns.  While the OP didn't say this in so many words, I gather
> that he found it to work fine for small jsonb values but not for large
> ones, which would be explained if it only fails for toasted values.
>
> In principle we could fix this by forcing the values to be detoasted
> before we put them into the tuplestore.  I'm not sure that we want to
> accept that overhead though.
>
> 			


Oh. Hah! makes sense. I learn something every day. Sorry for the noise.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com