Re: BUG #19431: limitation of the data type jsonb equals 8Kb

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(at)sbermarketing(dot)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