Regression on pg_restore to 16.0: DOMAIN not available to SQL function

From: Mark Hills <mark(at)xwax(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Regression on pg_restore to 16.0: DOMAIN not available to SQL function
Date: 2023-11-03 10:17:48
Message-ID: af9e90b5-721e-1286-a387-508da9469aa9@xwax.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message vignesh C 2023-11-03 10:45:45 Re: pg_upgrade and logical replication
Previous Message John Naylor 2023-11-03 10:11:50 Re: Pre-proposal: unicode normalized text