On Fri, 3 Nov 2023, Tom Lane wrote:
> Mark Hills <mark@xwax.org> writes:
> > 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 looks like your gen_hash() function is not proof against being
> run with a minimal search_path, which is how the restore script
> would call it.
Yes, that makes sense.
I suppose I didn't expect these functions to be invoked at all on
pg_restore, as seems to have been the case before, because...
> However, then it's not clear why it would've worked
> in 15.4 which does the same thing. I wonder whether you are
> using this function in a column default for the troublesome
> table.
Yes, it's just a simple DEFAULT:
CREATE TABLE authentic (
key hash NOT NULL UNIQUE DEFAULT gen_hash(32),
and so every row would have a value.
> If so, the discrepancy might be explained by this fix that I just got
> done writing a 16.1 release note for:
>
> <listitem>
> <!--
> Author: Andrew Dunstan <andrew@dunslane.net>
> Branch: master [276393f53] 2023-10-01 10:18:41 -0400
> Branch: REL_16_STABLE [910eb61b2] 2023-10-01 10:25:33 -0400
> -->
> <para>
> In <command>COPY FROM</command>, avoid evaluating column default
> values that will not be needed by the command (Laurenz Albe)
> </para>
>
> <para>
> This avoids a possible error if the default value isn't actually
> valid for the column. Previous releases did not fail in this edge
> case, so prevent v16 from doing so.
> </para>
> </listitem>
Indeed, that like a good match to this issue.
Is there a thread or link for this? Interested in the positive change that
had this side effect.
And I think this could imply that v16 can pg_dump a data set which itself
cannot restore? Imagining that might be considered a more serious bug.
Only needs a column default that invokes another function or type, and
that would seem relatively common.
> > It prompted me to separate the restore into steps:
> > * An initial "--schema-only" completes
> > * The "--data-only" when the error takes place
>
> Uh, *what* prompted you to do that? By and large, separating a
> restore into two steps creates more problems than it solves.
Only to try and bisect the problem in some way to try and make a
reasonable bug report :)
Thanks
--
Mark