On Fri, 3 Nov 2023, Tom Lane wrote:
> Mark Hills <m...@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 \.
