Feedback-ID: i76984098:Fastmail
Message-ID: <ae7a05a0-3efd-445b-ad2f-4c7a7f9ed15e@aklaver.com>
Date: Sat, 30 Nov 2024 19:36:03 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Errors when restoring backup created by pg_dumpall
To: PopeRigby <poperigby@mailbox.org>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org>
 <cba48d2c-0987-4233-a86f-2e693677efd3@aklaver.com>
 <9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org>
 <CAKFQuwaB5GuUNuyiUbS0ah-LYXepHLPnOHrP7g8RMz8ywOm3zQ@mail.gmail.com>
 <ff94f628-27af-4c3a-9590-cba38bb5d2d1@mailbox.org>
 <CAKFQuwYQUequkYVS8SLgC1L0TOdWNK=xVyxnPD9RPPwfvYRFSQ@mail.gmail.com>
 <ce6c2e3a-b8da-408c-be44-c4dc87747af3@mailbox.org>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <ce6c2e3a-b8da-408c-be44-c4dc87747af3@mailbox.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/ae7a05a0-3efd-445b-ad2f-4c7a7f9ed15e%40aklaver.com>
Precedence: bulk

On 11/30/24 19:26, PopeRigby wrote:
> On 11/30/24 18:41, David G. Johnston wrote:
>> On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:
>>
>>     On 11/30/24 17:27, David G. Johnston wrote:
>>>     On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org>
>>>     wrote:
>>>
>>>         On 11/29/24 17:47, Adrian Klaver wrote:
>>>
>>>             On 11/29/24 17:34, PopeRigby wrote:
>>>
>>>             psql:all.sql:4104: ERROR:  type "earth" does not exist
>>>             LINE 1:
>>>             ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
>>>
>>>             QUERY:  SELECT
>>>             cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
>>>             CONTEXT:  SQL function "ll_to_earth" during inlining
>>>              The earthdistance module is even getting added between
>>>             the table with the earth type is added, so shouldn't
>>>             there be no problem?
>>>
>>>
>>>     The fact that “earth” is not schema qualified leads me to suspect
>>>     you are getting bit by safe search_path environment rules.
>>>
>>>     David J.
>>
>>     Ah. How can I fix that?
>>
>> Since you are past the point of fixing the source to produce valid 
>> dumps…that leaves finding the places in the text the lack the schema 
>> qualification and manually adding them in.
>>
>> David J.
>>
> Oh boy. How can I prevent this from happening again?
> 

In future schema qualify all references.

For now in the dump file you could search for

SELECT pg_catalog.set_config('search_path', '', false);

and set to

SELECT pg_catalog.set_config('search_path', 'public', false);


-- 
Adrian Klaver
adrian.klaver@aklaver.com