public inbox for [email protected]
help / color / mirror / Atom feedRe: Errors when restoring backup created by pg_dumpall
7+ messages / 4 participants
[nested] [flat]
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 03:36 PopeRigby <[email protected]>
2024-12-01 03:45 ` Re: Errors when restoring backup created by pg_dumpall David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: PopeRigby @ 2024-12-01 03:36 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On 11/30/24 18:41, David G. Johnston wrote:
> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>
> On 11/30/24 17:27, David G. Johnston wrote:
>> On Saturday, November 30, 2024, PopeRigby <[email protected]>
>> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
@ 2024-12-01 03:45 ` David G. Johnston <[email protected]>
2024-12-01 03:47 ` Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 03:58 ` Re: Errors when restoring backup created by pg_dumpall Tom Lane <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: David G. Johnston @ 2024-12-01 03:45 UTC (permalink / raw)
To: PopeRigby <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
> On 11/30/24 18:41, David G. Johnston wrote:
>
> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>
>> On 11/30/24 17:27, David G. Johnston wrote:
>>
>> On Saturday, November 30, 2024, PopeRigby <[email protected]> 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(rad
>>>> ians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(rad
>>>> ians($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/fcb59eb6c22c6051800e06a0ec482b
> 49#file-redacted_all-sql-L4111
>
> Why is it not finding it? I queried public and earth was in there.
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 03:45 ` Re: Errors when restoring backup created by pg_dumpall David G. Johnston <[email protected]>
@ 2024-12-01 03:47 ` PopeRigby <[email protected]>
2024-12-01 03:51 ` Re: Errors when restoring backup created by pg_dumpall David G. Johnston <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: PopeRigby @ 2024-12-01 03:47 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On 11/30/24 19:45, David G. Johnston wrote:
> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>
> On 11/30/24 18:41, David G. Johnston wrote:
>> On Saturday, November 30, 2024, PopeRigby <[email protected]>
>> wrote:
>>
>> On 11/30/24 17:27, David G. Johnston wrote:
>>> On Saturday, November 30, 2024, PopeRigby
>>> <[email protected]> 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
> <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.
>
>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 03:45 ` Re: Errors when restoring backup created by pg_dumpall David G. Johnston <[email protected]>
2024-12-01 03:47 ` Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
@ 2024-12-01 03:51 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2024-12-01 03:51 UTC (permalink / raw)
To: PopeRigby <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On Saturday, November 30, 2024, PopeRigby <[email protected]> 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.
>
> 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.
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 03:45 ` Re: Errors when restoring backup created by pg_dumpall David G. Johnston <[email protected]>
@ 2024-12-01 03:58 ` Tom Lane <[email protected]>
2024-12-01 20:05 ` Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-12-01 03:58 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: PopeRigby <[email protected]>; Adrian Klaver <[email protected]>; pgsql-general
"David G. Johnston" <[email protected]> writes:
> 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.
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 protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 03:45 ` Re: Errors when restoring backup created by pg_dumpall David G. Johnston <[email protected]>
2024-12-01 03:58 ` Re: Errors when restoring backup created by pg_dumpall Tom Lane <[email protected]>
@ 2024-12-01 20:05 ` PopeRigby <[email protected]>
2024-12-01 21:08 ` Re: Errors when restoring backup created by pg_dumpall Adrian Klaver <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: PopeRigby @ 2024-12-01 20:05 UTC (permalink / raw)
To: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On 11/30/24 19:58, Tom Lane wrote:
> "David G. Johnston" <[email protected]> writes:
>> 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.
> 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 protected]
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 03:45 ` Re: Errors when restoring backup created by pg_dumpall David G. Johnston <[email protected]>
2024-12-01 03:58 ` Re: Errors when restoring backup created by pg_dumpall Tom Lane <[email protected]>
2024-12-01 20:05 ` Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
@ 2024-12-01 21:08 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2024-12-01 21:08 UTC (permalink / raw)
To: PopeRigby <[email protected]>; Tom Lane <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-general
On 12/1/24 12:05, PopeRigby wrote:
> On 11/30/24 19:58, Tom Lane wrote:
>> "David G. Johnston" <[email protected]> writes:
>>> 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.
>> 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 protected]
>
> 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
>
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 protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-12-01 21:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 03:45 ` David G. Johnston <[email protected]>
2024-12-01 03:47 ` PopeRigby <[email protected]>
2024-12-01 03:51 ` David G. Johnston <[email protected]>
2024-12-01 03:58 ` Tom Lane <[email protected]>
2024-12-01 20:05 ` PopeRigby <[email protected]>
2024-12-01 21:08 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox