Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tHawI-008eji-Lw for pgsql-general@arkaria.postgresql.org; Sun, 01 Dec 2024 03:48:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tHawG-004VL7-4j for pgsql-general@arkaria.postgresql.org; Sun, 01 Dec 2024 03:48:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tHawF-004VIw-NU for pgsql-general@lists.postgresql.org; Sun, 01 Dec 2024 03:48:04 +0000 Received: from mout-p-103.mailbox.org ([2001:67c:2050:0:465::103]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tHawD-000Q2Q-FJ for pgsql-general@postgresql.org; Sun, 01 Dec 2024 03:48:04 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-p-103.mailbox.org (Postfix) with ESMTPS id 4Y1CYV1kJXz9shX; Sun, 1 Dec 2024 04:47:58 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mailbox.org; s=mail20150812; t=1733024878; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=FiCEyRzrZGl2Jsa6AEpHk6kMEEDn+i8o7ce6ZGt7yWY=; b=GMzLQH868bxrrKWlNUF9Oz6xNukUqTuj5spqJd3500WPfFJsVBDDC72UdV2Jq2qmGNzLkv WOJOfC8xi8fOkQt/XPkrBGWnJYlV2KY6qpCIQIBrfrEP8q/IbVEISMWGZgQvmJwvGP3cIp OzVTmFJi7X52Cpi16+O8b41y2xP7xLJgWb6QLfd0SL3V6NPyn1DIH8PhfqiqAQ44yRACiv 4MDy3ST56sSOnXJ8HV0p0k126yTHXojROINLNN4LJVXZyxJ8xVqnqze7dkTiM9zrjoFI+r 5XB8RpxcGmSj30ztjwFuxzXqYPEYDpJJWLsFYGvfj7/RDkgcIOYGjgxiCJXpEg== Content-Type: multipart/alternative; boundary="------------Jz0yTgrpID4CFcMbwF0gGAxg" Message-ID: Date: Sat, 30 Nov 2024 19:47:55 -0800 MIME-Version: 1.0 Subject: Re: Errors when restoring backup created by pg_dumpall To: "David G. Johnston" Cc: Adrian Klaver , "pgsql-general@postgresql.org" References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org> <9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org> <41791b6d-aaf5-4fed-9cc3-e89bc49e8637@mailbox.org> Content-Language: en-US From: PopeRigby In-Reply-To: X-MBO-RS-ID: 85c986b23db80992873 X-MBO-RS-META: cyp6czpp34cdpaemxqcyb8m6szfx1fiu List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Jz0yTgrpID4CFcMbwF0gGAxg Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/30/24 19:45, David G. Johnston wrote: > On Saturday, November 30, 2024, PopeRigby wrote: > > On 11/30/24 18:41, David G. Johnston wrote: >> On Saturday, November 30, 2024, PopeRigby >> wrote: >> >> On 11/30/24 17:27, David G. Johnston wrote: >>> On Saturday, November 30, 2024, PopeRigby >>> 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. > > > 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. --------------Jz0yTgrpID4CFcMbwF0gGAxg Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 11/30/24 19:45, David G. Johnston wrote:
On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> 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 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.


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.

--------------Jz0yTgrpID4CFcMbwF0gGAxg--