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. 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. 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>
> 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.
regards, tom lane