From: | Mark Hills <mark(at)xwax(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function |
Date: | 2023-11-03 15:43:43 |
Message-ID: | 17a010b1-231e-507c-d71f-2bce7b0abffc@xwax.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, 3 Nov 2023, Tom Lane wrote:
> Mark Hills <mark(at)xwax(dot)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(at)dunslane(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Vik Fearing | 2023-11-03 15:45:03 | Re: [PATCH] Add XMLText function (SQL/XML X038) |
Previous Message | Daniel Gustafsson | 2023-11-03 15:30:17 | Re: [PATCH] Add XMLText function (SQL/XML X038) |