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

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 \.