Re: pg_restore error on function

Re: pg_restore error on function

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  .

in the dump file does not implicitly create the schema as a

create table .

would?


>
>>
>> The only workaround I can think of is to dump in plain text and then edit the dump file.
>>
>>
>

Re: pg_restore error on function

"David G. Johnston"  writes:
> On Thu, Nov 3, 2022 at 8:39 AM Post Gresql  wrote:
>> Could it be that the
>> create function  .
>> in the dump file does not implicitly create the schema as a
>> create table .
>> 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

Re: pg_restore error on function

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  .

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

Re: pg_restore error on function

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.

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.

Re: pg_restore error on function

Re: 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

Re: pg_restore error on function

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

Re: pg_restore error on function

Re: pg_restore error on function

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)