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(at)xwax(dot)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 \.