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

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;

On 2023-11-03 Fr 06:17, Mark Hills wrote:
> 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?)


In general you should use pg_dump from the version you want to restore 
into. Dumps from earlier versions might work in some cases, but there is 
no guarantee.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

  pg_restore: error: could not execute query: ERROR:  type "hash" does not exist
  LINE 7:         )::hash;
  [...]
  CONTEXT:  SQL function "gen_hash" during inlining

--
-- Relevant SQL declarations
--

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

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

Mark Hills <mark@xwax.org> writes:
> On Fri, 3 Nov 2023, Tom Lane wrote:
>> 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.

Right, so the 910eb61b2 fix explains it.  I guess I'd better
expand the release note entry, because we'd not foreseen this
particular failure mode.

> Is there a thread or link for this? Interested in the positive change that
> had this side effect.

You could look at the commit:

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=910eb61b2

Our modern practice is for the commit log message to link to the mailing
list discussion that led up to the commit, and this one does so:

> Discussion: https://postgr.es/m/75a7b7483aeb331aa017328d606d568fc715b90d.camel@cybertec.at

That message says

>> Bisecting shows that the regression was introduced by commit 9f8377f7a2,
>> which introduced DEFAULT values for COPY FROM.

            regards, tom lane

On Fri, 3 Nov 2023, Tom Lane wrote:

> Mark Hills <mark@xwax.org> writes:
> > On Fri, 3 Nov 2023, Tom Lane wrote:
> >> 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.
> 
> Right, so the 910eb61b2 fix explains it.  I guess I'd better
> expand the release note entry, because we'd not foreseen this
> particular failure mode.

Indeed, and curiosity got the better of me so I constructed a minimal test 
case (see below)

This minimal test demonstrates a database which will pg_dump but cannot 
restore (custom with pg_restore, or plain SQL with psql.)

I assumed I'd need at least one row of data to trigger the bug (to call on 
a default), but that's not the case and here it is with an empty table.

I then tested REL_16_STABLE branch (e24daa94b) the problem does not occur, 
as expected.

Also, the stable branch version was able to restore the pg_dump from 16.0 
release, which is as expected and is probably important (and helpful)

Thanks

-- 
Mark


==> test.sql <==
CREATE FUNCTION inner()
RETURNS integer AS
$$
    SELECT 1;
$$ LANGUAGE SQL;

CREATE FUNCTION outer()
RETURNS integer AS
$$
    SELECT inner();
$$ LANGUAGE SQL;

CREATE TABLE test (
    v integer NOT NULL DEFAULT outer()
);


$ createdb test
$ psql test < test.sql


$ pg_dump --format custom --file test.pgdump test
$ createdb restore
$ pg_restore --dbname restore test.pgdump
pg_restore: error: could not execute query: ERROR:  function inner() does not exist
LINE 2:     SELECT inner();
                   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.
QUERY:
    SELECT inner();

CONTEXT:  SQL function "outer" during inlining
Command was: COPY public.test (v) FROM stdin;
pg_restore: warning: errors ignored on restore: 1


$ pg_dump --format plain --file test.pgdump test
$ createdb restore
$ psql restore < test.pgdump
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
ERROR:  function inner() does not exist
LINE 2:     SELECT inner();
                   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.
QUERY:
    SELECT inner();

CONTEXT:  SQL function "outer" during inlining
invalid command \.

Mark Hills <mark@xwax.org> writes:
> On Fri, 3 Nov 2023, Tom Lane wrote:
>> Right, so the 910eb61b2 fix explains it.  I guess I'd better
>> expand the release note entry, because we'd not foreseen this
>> particular failure mode.

> Indeed, and curiosity got the better of me so I constructed a minimal test 
> case (see below)

I checked this against 16 branch tip (about to become 16.1),
and it works, as expected.

> I assumed I'd need at least one row of data to trigger the bug (to call on 
> a default), but that's not the case and here it is with an empty table.

Right.  The step 16.0 is taking that fails is not evaluating the
default expression, but merely prepping it for execution during COPY
startup.  This error occurs while trying to inline the inline-able
outer() function.

We worked around this by skipping the expression prep step when it's
clear from the COPY arguments that we won't need it, which should
be true for all of pg_dump's uses of COPY.

            regards, tom lane