pg_restore error on function

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:

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.

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 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?

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.