Re: pg_restore error on function

On Thu, Nov 3, 2022 at 8:39 AM Post Gresql <postgresql(at)taljaren(dot)se> wrote:

>
> On 2022-11-03 15:43, Adrian Klaver wrote:
> > On 11/3/22 07:28, Post Gresql wrote:
> >> Hello
> >>
> >> I first successfully ran
> >>
> >> pg_dump -U postgres -b -E UTF8 -f my_dump --format=custom -n my_schema
> --no-owner -v my_db
> >>
> >> but then
> >>
> >> pg_restore --single-transaction -v -U postgres -O -e -d my_other_db
> my_dump
> >>
> >> failed with
> >>
> >> pg_restore: connecting to database for restore
> >> pg_restore: creating FUNCTION "my_schema.update_b()"
> >> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> >> pg_restore: [archiver (db)] Error from TOC entry 7142; 1255 8618854
> FUNCTION update_b() previous_owner
> >> pg_restore: [archiver (db)] could not execute query: ERROR: schema
> my_schema" does not exist
> >> Command was: CREATE FUNCTION "my_schema".update_b() RETURNS void
> >> LANGUAGE plpgsql
> >> AS $$
> >> BEGIN
> >> PERFORM pg_advisory_xact_lock(1);
> >>
> >> INSERT INTO ...;
> >> END;
> >> $$;
> >>
> >>
> >> This is PG version 10.19
> >>
> >> Is there a solution for this (apart from upgrading to a newer PG
> version)?
> >
> > Was there another error before this?
> >
> > Did the -v show the schema my_schema being created?
>
>
> No there were no other output apart from what I showed above.
>
> No, no mention of creating the schema.
>
>
> Btw. it worked fine when I used plain text output and moved the part of
> creating the function to near the end of the dump file.
>
> Could it be that the
>
> create function <schema>.<function name>
>
> in the dump file does not implicitly create the schema as a
>
> create table <schema>.<table name>
>
> would?
>
>
I don't know where you got the idea that a schema is implicitly created via
create table...a schema is never implicitly created.

It is unlikely, though possible (casts were just fixed in 10.20), that
you've discovered a dependency tracking bug. At this point you would need
to produce and show a self-contained test case to provide further help.
The information you have shown so far indeed looks problematic but
insufficient to diagnose further.

pg_dump -n schema is documented to dump both the schema and its objects so
pg_restore should be restoring both, and should be restoring schemas before
trying to restore most anything else.

You could try playing with pg_restore -L and try to get a sequence that
works.

David J.