Re: Errors when restoring backup created by pg_dumpall

On 12/1/24 13:14, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com