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;
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 |