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

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ň