Regression on pg_restore to 16.0: DOMAIN not available to SQL function - Mailing list pgsql-hackers

I'm having errors restoring with pg_restore to v16.0, it appears to be a 
regression or bug. The same file restored to v15.4 without problem.

During the restore:

  pg_restore: error: could not execute query: ERROR:  type "hash" does not exist
  LINE 7:         )::hash;
  [...]
  CONTEXT:  SQL function "gen_hash" during inlining

It prompted me to separate the restore into steps:

* An initial "--schema-only" completes
* The "--data-only" when the error takes place

I also double-checked for no mismatch of client/server etc.

For now, I can use 15.4 for this one-off task so will have to kick this 
can down the road.

But I think it worth reporting that something in 16.0 appears to be 
failing on valid data (or maybe there is an incompatibility with a dump 
from 13.5?)

Thanks

-- 
Mark



$ export DUMP="$HOME/tmp/production.pgdump"


$ pg_restore --dbname=stattrx --no-owner --no-privileges --schema-only --verbose --exit-on-error $DUMP
[succeeds, no errors]


$ pg_restore --dbname=stattrx --no-owner --no-privileges --data-only --verbose --exit-on-error $DUMP
pg_restore: connecting to database for restore
pg_restore: processing data for table "public.authentic"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4183; 0 58291 TABLE DATA authentic postgres
pg_restore: error: could not execute query: ERROR:  type "hash" does not exist
LINE 7:         )::hash;
                   ^
QUERY:
    SELECT
        substring(
            regexp_replace(
                encode(gen_random_bytes(1024), 'base64'),
                '[^a-zA-Z0-9]', '', 'g') for $1
        )::hash;

CONTEXT:  SQL function "gen_hash" during inlining
Command was: COPY public.authentic (key, generated, peer, expires, studio) FROM stdin;


$ pg_restore --version
pg_restore (PostgreSQL) 16.0


$ pg_restore --list $DUMP
;
; Archive created at 2023-10-30 06:47:01 GMT
;     dbname: production
;     TOC Entries: 227
;     Compression: gzip
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 13.5
;     Dumped by pg_dump version: 13.5
;
;
; Selected TOC Entries:
;
4; 3079 57533 EXTENSION - btree_gist
4212; 0 0 COMMENT - EXTENSION btree_gist
2; 3079 492253 EXTENSION - ltree
4213; 0 0 COMMENT - EXTENSION ltree
3; 3079 58156 EXTENSION - pgcrypto
4214; 0 0 COMMENT - EXTENSION pgcrypto
1022; 1247 58194 DOMAIN public handle postgres
1026; 1247 58197 DOMAIN public hash postgres
[...]
504; 1255 58233 FUNCTION public gen_hash(integer) postgres
[...]


--
-- Relevant SQL declarations
--

CREATE DOMAIN hash AS text
    CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$');

CREATE OR REPLACE FUNCTION gen_hash(int)
RETURNS hash AS
$$
    SELECT
        substring(
            regexp_replace(
                encode(gen_random_bytes(1024), 'base64'),
                '[^a-zA-Z0-9]', '', 'g') for $1
        )::hash;
$$ LANGUAGE SQL;