pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19431: limitation of the data type jsonb equals 8Kb
4+ messages / 3 participants
[nested] [flat]
* BUG #19431: limitation of the data type jsonb equals 8Kb
@ 2026-03-12 12:11 PG Bug reporting form <noreply@postgresql.org>
2026-03-12 14:09 ` Re: BUG #19431: limitation of the data type jsonb equals 8Kb Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-03-12 12:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: perovaa@sbermarketing.ru
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19431: limitation of the data type jsonb equals 8Kb
2026-03-12 12:11 BUG #19431: limitation of the data type jsonb equals 8Kb PG Bug reporting form <noreply@postgresql.org>
@ 2026-03-12 14:09 ` Andrew Dunstan <andrew@dunslane.net>
2026-03-12 15:55 ` Re: BUG #19431: limitation of the data type jsonb equals 8Kb Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Andrew Dunstan @ 2026-03-12 14:09 UTC (permalink / raw)
To: perovaa@sbermarketing.ru; pgsql-bugs@lists.postgresql.org
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".
This is a classic pitfall documented in the PostgreSQL manual under
PL/pgSQL plan caching.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19431: limitation of the data type jsonb equals 8Kb
2026-03-12 12:11 BUG #19431: limitation of the data type jsonb equals 8Kb PG Bug reporting form <noreply@postgresql.org>
2026-03-12 14:09 ` Re: BUG #19431: limitation of the data type jsonb equals 8Kb Andrew Dunstan <andrew@dunslane.net>
@ 2026-03-12 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-12 17:23 ` Re: BUG #19431: limitation of the data type jsonb equals 8Kb Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2026-03-12 15:55 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: perovaa@sbermarketing.ru; pgsql-bugs@lists.postgresql.org
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.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19431: limitation of the data type jsonb equals 8Kb
2026-03-12 12:11 BUG #19431: limitation of the data type jsonb equals 8Kb PG Bug reporting form <noreply@postgresql.org>
2026-03-12 14:09 ` Re: BUG #19431: limitation of the data type jsonb equals 8Kb Andrew Dunstan <andrew@dunslane.net>
2026-03-12 15:55 ` Re: BUG #19431: limitation of the data type jsonb equals 8Kb Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-03-12 17:23 ` Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 4+ messages in thread
From: Andrew Dunstan @ 2026-03-12 17:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: perovaa@sbermarketing.ru; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-03-12 17:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-12 12:11 BUG #19431: limitation of the data type jsonb equals 8Kb PG Bug reporting form <noreply@postgresql.org>
2026-03-12 14:09 ` Andrew Dunstan <andrew@dunslane.net>
2026-03-12 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-12 17:23 ` Andrew Dunstan <andrew@dunslane.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox