Thread: pg_restore error on function
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)?
The only workaround I can think of is to dump in plain text and then edit the dump file.
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? > > The only workaround I can think of is to dump in plain text and then > edit the dump file. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/3/22 09:28, Post Gresql wrote:
What was the complete pg_dump command?
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)?
What was the complete pg_dump command?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 11/3/22 07:45, Ron wrote: > On 11/3/22 09: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 >> > What was the *complete* pg_dump command? I'm going to say the above. -- Adrian Klaver adrian.klaver@aklaver.com
On 2022-11-03 15:57, Adrian Klaver wrote: > On 11/3/22 07:45, Ron wrote: >> On 11/3/22 09: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 >>> > >> What was the *complete* pg_dump command? > > I'm going to say the above. > That is the complete command (actual names redacted)
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 dumpfile. 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? > >> >> The only workaround I can think of is to dump in plain text and then edit the dump file. >> >> >
On Thu, Nov 3, 2022 at 8:39 AM Post Gresql <postgresql@taljaren.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.
On 11/3/22 08:38, Post Gresql 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. The -v to pg_restore should output the progress: https://www.postgresql.org/docs/current/app-pgrestore.html -v --verbose Specifies verbose mode. This will cause pg_restore to output detailed object comments and start/stop times to the output file, and progress messages to standard error. Repeating the option causes additional debug-level messages to appear on standard error. Something like: pg_dump -d test -U postgres -Fc -n test -f test_sch.out pg_restore -d t -U postgres -v test_sch.out pg_restore: connecting to database for restore pg_restore: creating SCHEMA "test" pg_restore: creating TABLE "test.c2" pg_restore: creating TABLE "test.supplies" pg_restore: creating SEQUENCE "test.supplies_id_seq" pg_restore: creating SEQUENCE OWNED BY "test.supplies_id_seq" pg_restore: creating TABLE "test.up_test" pg_restore: creating DEFAULT "test.supplies id" pg_restore: processing data for table "test.c2" pg_restore: processing data for table "test.supplies" pg_restore: processing data for table "test.up_test" pg_restore: executing SEQUENCE SET supplies_id_seq pg_restore: creating CONSTRAINT "test.c2 pk1" pg_restore: creating CONSTRAINT "test.supplies supplies_pkey" > > No, no mention of creating the schema. As you see the schema was created first. > > > 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> Well the schema should have already been created. Assuming this: pg_restore: connecting to database for restore pg_restore: creating FUNCTION "my_schema.update_b()" was not the result of an edit then something is not working correctly. What is my_schema.update_b() doing? > > in the dump file does not implicitly create the schema as a > > create table <schema>.<table name> > > would? > > >> >>> >>> The only workaround I can think of is to dump in plain text and then >>> edit the dump file. >>> >>> >> -- Adrian Klaver adrian.klaver@aklaver.com
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Nov 3, 2022 at 8:39 AM Post Gresql <postgresql@taljaren.se> wrote:
>> 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.
Indeed.
> 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.
Yeah. The explanation would seem to be that pg_dump didn't dump
"CREATE SCHEMA my_schema", or that pg_restore chose to skip that
object, or that one of them mistakenly thought that the CREATE
FUNCTION should be issued first. None of those things should happen
AFAICS, and none of them does happen in a simple test case, so
there's something going on here that we've not been shown.
regards, tom lane
On 11/3/22 09:57, Adrian Klaver wrote: > On 11/3/22 07:45, Ron wrote: >> On 11/3/22 09: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 >>> > >> What was the *complete* pg_dump command? > > I'm going to say the above. I'm embarrassed. -- Angular momentum makes the world go 'round.