Subject: Re: Errors when restoring backup created by pg_dumpall

From: PopeRigby <[email-masked]>
Date: Sat, 30 Nov 2024 19:36:46 -0800

On 11/30/24 18:41, David G. Johnston wrote:
> On Saturday, November 30, 2024, PopeRigby <[email-masked]> wrote:
>
>     On 11/30/24 17:27, David G. Johnston wrote:
>>     On Saturday, November 30, 2024, PopeRigby <[email-masked]>
>>     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 also, it's the schema is specified as public on this line: 
https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49#file-redacted_all-sql-L4111

Why is it not finding it? I queried public and earth was in there.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 30 Nov 2024 20:45:55 -0700

On Saturday, November 30, 2024, PopeRigby <[email-masked]> wrote:

>>>> LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
>>>>
>>>> QUERY:  SELECT cube(cube(cube(earth()*cos(rad
>>>> ians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(rad
>>>> ians($2))),earth()*sin(radians($1)))::earth
>>>>  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?
>>

Ok, so the error is not emanating from your code but rather the body of the
ll_to_earth function defined in the earthdistance extension.

David J.

---

From: PopeRigby <[email-masked]>
Date: Sat, 30 Nov 2024 19:47:55 -0800

On 11/30/24 19:45, David G. Johnston wrote:
>>>                  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.
>>
>     <https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49#file-redacted_all-sql-L4111>
>
> Ok, so the error is not emanating from your code but rather the body 
> of the ll_to_earth function defined in the earthdistance extension.
>
> David J.
>
By code do you mean my sql file created by pg_dumpall? Sorry, I'm just a 
self-hoster so I'm not very well versed in Postgres. I'm just trying to 
get my server back online.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 30 Nov 2024 20:51:40 -0700

On Saturday, November 30, 2024, PopeRigby <[email-masked]> wrote:

>
> Ok, so the error is not emanating from your code but rather the body of
> the ll_to_earth function defined in the earthdistance extension.
>
> David J.
>
> self-hoster so I'm not very well versed in Postgres. I'm just trying to get
> my server back online.
>

Yes, your “code” is the user contents of the SQL file.

Your best bet is probably to locate the various “set_config(‘search_path’,
‘’)” commands and add “public” to them.  Since you installed earthdistance
to public this should bridge the gap.

David J.

---

From: Tom Lane <[email-masked]>
Date: Sat, 30 Nov 2024 22:58:13 -0500

> ll_to_earth function defined in the earthdistance extension.

Yeah.  That is

CREATE FUNCTION ll_to_earth(float8, float8)
RETURNS earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';

which is problematic: the function calls are not schema-qualified and
there's not a really easy way to fix that.  There's work afoot to fix
that [1], but it's not committed yet let alone in any shipping
version.  Nonetheless, your best bet for fixing this might be
to install the earthdistance 1.2 files from the latest patchset in
that thread.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/[email-masked]

---

From: PopeRigby <[email-masked]>
Date: Sun, 1 Dec 2024 12:05:03 -0800

> [1] https://www.postgresql.org/message-id/flat/[email-masked]

I've applied the following patch to postgres:

https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

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

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 1 Dec 2024 13:08:28 -0800

On 12/1/24 12:05, PopeRigby wrote:
>>> of the
>> AS 'SELECT 
>> cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';
>>
>> [1] 
>> https://www.postgresql.org/message-id/flat/[email-masked]
> 

The issue is still this:

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

in the pg_dumpall output.

As was suggested before change the above to:

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

in the pg_dumpall output file.

-- 
Adrian Klaver
[email-masked]