Search Postgresql Archives

Re: pg_restore error on function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Nov 3, 2022 at 8:39 AM Post Gresql <postgresql@xxxxxxxxxxx> 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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux