Feedback-ID: i76984098:Fastmail
Message-ID: <4ab662d8-57cc-471f-8a58-cfd71d1cea22@aklaver.com>
Date: Sun, 1 Dec 2024 13:36:36 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Errors when restoring backup created by pg_dumpall
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PopeRigby <poperigby@mailbox.org>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
 "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>
 <41791b6d-aaf5-4fed-9cc3-e89bc49e8637@mailbox.org>
 <CAKFQuwbe4TSU_LxsrGxgcv4DfQq2x-UNoyKx-ABy36PhvbGCdg@mail.gmail.com>
 <1257068.1733025493@sss.pgh.pa.us>
 <06425038-e012-4bac-aec1-d9541436f893@mailbox.org>
 <92405f98-7f0c-4442-8252-697352daefc1@aklaver.com>
 <1470486.1733087658@sss.pgh.pa.us>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <1470486.1733087658@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/4ab662d8-57cc-471f-8a58-cfd71d1cea22%40aklaver.com>
Precedence: bulk

On 12/1/24 13:14, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 12/1/24 12:05, PopeRigby wrote:
>>> I'm still getting this error:
>>>
>>> psql:all.sql:4102: ERROR:  type "earth" does not exist
>>> LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
> 
>> The issue is still this:
>> SELECT pg_catalog.set_config('search_path', '', false);
>> in the pg_dumpall output.
> 
> We've done that for some time, though.
> 
>> As was suggested before change the above to:
>> SELECT pg_catalog.set_config('search_path', 'public', false);
>> in the pg_dumpall output file.
> 
> I'm betting that won't help.  The new-in-17 behavior is that
> maintenance commands such as CREATE INDEX internally force
> a "safe" search_path, regardless of the prevailing setting.
> 
> It would be useful to know what is the command at line 4102
> of all.sql.

It is here:

https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49

CREATE TABLE public.geodata_places (
     id integer NOT NULL,
     name character varying(200) NOT NULL,
     longitude double precision NOT NULL,
     latitude double precision NOT NULL,
     "countryCode" character(2) NOT NULL,
     "admin1Code" character varying(20),
     "admin2Code" character varying(80),
     "modificationDate" date NOT NULL,
     "earthCoord" public.earth GENERATED ALWAYS AS 
(public.ll_to_earth(latitude, longitude)) STORED,
     "admin1Name" character varying,
     "admin2Name" character varying,
     "alternateNames" character varying
);


> 
> 			regards, tom lane

-- 
Adrian Klaver
adrian.klaver@aklaver.com