Thread

1. BUG #19084: dump/restore table doesn't work with GENARETED column and custom type and cast in function

   PG Bug reporting form <noreply@postgresql.org> — 2025-10-13T13:32:33Z

   The following bug has been logged on the website:
   
   Bug reference:      19084
   Logged by:          David Turoň
   Email address:      turon.david@seznam.cz
   PostgreSQL version: 17.6
   Operating system:   AlmaLinux release 9.0
   Description:        
   
   Good morning,
   
   i have issue with dump/restore custom type used as GENERATED column, here is
   example:
   
   --test table with GENEREATED column CUSTOM TYPE WITH IMMUTABLE FUNCTION
   
   psql postgres postgres
   psql (17.6)
   
   CREATE DATABASE test;
   \c test
   
   --custom shell type storing data as bigint, but in json cast as text
   -- SELECT jsonb_build_object('test',1::test_type);
   -- jsonb_build_object
   -- ------------------
   -- {"test": "1"}
   
   
   
   CREATE TYPE public.test_type;
   
   
   CREATE OR REPLACE FUNCTION public.test_type_in(cstring)
   RETURNS public.test_type
   LANGUAGE internal
   IMMUTABLE
   PARALLEL SAFE
   STRICT AS $function$int8in$function$;
   
   
   
   CREATE OR REPLACE FUNCTION public.test_type_out(public.test_type)
   RETURNS cstring
   LANGUAGE internal
   IMMUTABLE PARALLEL SAFE
   STRICT AS $function$int8out$function$;
   
   
   CREATE TYPE public.test_type(INPUT=test_type_in, OUTPUT=test_type_out,
   LIKE=bigint);
   
   
   CREATE CAST (bigint AS public.test_type) WITHOUT FUNCTION AS IMPLICIT;
   CREATE CAST (public.test_type AS bigint) WITHOUT FUNCTION AS IMPLICIT;
   
   
   CREATE OR REPLACE FUNCTION public.test_type_int_to_test_type(int)
   RETURNS public.test_type
   LANGUAGE SQL
   IMMUTABLE PARALLEL SAFE
   STRICT AS $function$
   SELECT $1::bigint::test_type;
   $function$;
   
   CREATE CAST (int AS public.test_type) WITH FUNCTION
   public.test_type_int_to_test_type(int) AS IMPLICIT;
   
   
   
   -- custom immutable function used in GENERATED column with custom type and
   custom cast
   CREATE FUNCTION f(data jsonb) RETURNS test_type[] AS $$ SELECT
   array_agg((e->>'item')::test_type) FROM jsonb_array_elements(data->'test')
   AS e $$ LANGUAGE SQL IMMUTABLE;
   
   -- table with custom type, using custom function above - transfer json data
   to array of custom type
   CREATE TABLE test(data jsonb, numbers public.test_type[] GENERATED ALWAYS AS
   (f(data)) STORED);
   
   INSERT INTO test(data) VALUES ('{"test": [{"item": "1"}, {"item": "2"},
   {"item": "3"}]}');
   
   TABLE test;
                             data                           | numbers
   ---------------------------------------------------------+---------
    {"test": [{"item": "1"}, {"item": "2"}, {"item": "3"}]} | {1,2,3}
   
   --dump works well
   pg_dump -U postgres -Fc test > /home/test.pg_dump
   
   
   --but restore doesn't work
   pg_restore -U postgres -Fc -d test /home/test.pg_dump
   pg_restore: while PROCESSING TOC:
   pg_restore: from TOC entry 209; 1259 16488 TABLE test postgres
   pg_restore: error: could not execute query: ERROR:  type "test_type" does
   not exist
   LINE 1:  SELECT array_agg((e->>'item')::test_type) FROM jsonb_array_...
                                           ^
   QUERY:   SELECT array_agg((e->>'item')::test_type) FROM
   jsonb_array_elements(data->'test') AS e
   CONTEXT:  SQL function "f" during inlining
   Command was: CREATE TABLE public.test (
       data jsonb,
       numbers public.test_type[] GENERATED ALWAYS AS (public.f(data)) STORED
   );
   
   
   pg_restore: error: could not execute query: ERROR:  relation "public.test"
   does not exist
   Command was: ALTER TABLE public.test OWNER TO postgres;
   
   pg_restore: from TOC entry 4046; 0 16488 TABLE DATA test postgres
   pg_restore: error: could not execute query: ERROR:  relation "public.test"
   does not exist
   Command was: COPY public.test (data) FROM stdin;
   pg_restore: warning: errors ignored on restore: 3
   
   Hotfix is now cast inside function to bigint instead of custom test_type
   type. Also upgrade postgres 14->17 fails and is maybe hard to detect this
   usage somewhere in database. Thanks for help.
   
   David Turoň
   
   

2. Re: BUG #19084: dump/restore table doesn't work with GENARETED column and custom type and cast in function

   David G. Johnston <david.g.johnston@gmail.com> — 2025-10-13T15:19:46Z

   On Mon, Oct 13, 2025, 10:38 PG Bug reporting form <noreply@postgresql.org>
   wrote:
   
   > The following bug has been logged on the website:
   >
   > Bug reference:      19084
   > Logged by:          David Turoň
   > Email address:      turon.david@seznam.cz
   > PostgreSQL version: 17.6
   > Operating system:   AlmaLinux release 9.0
   > Description:
   >
   >
   > pg_restore: error: could not execute query: ERROR:  type "test_type" does
   > not exist
   > LINE 1:  SELECT array_agg((e->>'item')::test_type) FROM jsonb_array_...
   >
   
   
   This is an application bug, not Postgres.  You relied on the public schema
   being in search_path but didn't declare that fact via SET on the function.
   Either use SET, or schema-qualify objects so you don't have to rely on
   search_path at all.
   
   David J.
   

3. Re: BUG #19084: dump/restore table doesn't work with GENARETED column and custom type and cast in function

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-13T15:29:12Z

   PG Bug reporting form <noreply@postgresql.org> writes:
   > CREATE FUNCTION f(data jsonb) RETURNS test_type[] AS $$ SELECT
   > array_agg((e->>'item')::test_type) FROM jsonb_array_elements(data->'test')
   > AS e $$ LANGUAGE SQL IMMUTABLE;
   
   The problem with this function is that it doesn't work unless
   type public.test_type is in the search_path.  Which it is not
   during pg_restore (at least not with moderately-recent versions).
   
   You could either schema-qualify the reference to test_type,
   or switch the function to new-style SQL:
   
   CREATE FUNCTION f(data jsonb) RETURNS test_type[]
   IMMUTABLE
   BEGIN ATOMIC
   SELECT array_agg((e->>'item')::test_type)
     FROM jsonb_array_elements(data->'test') AS e;
   END;
   
   If you use that syntax then the test_type reference is parsed at
   function definition time instead of function execution, and
   everything is a lot safer.
   
   (The same goes for your other SQL-language function.)
   
   			regards, tom lane