MIME-Version: 1.0
In-Reply-To: <41791b6d-aaf5-4fed-9cc3-e89bc49e8637@mailbox.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>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 30 Nov 2024 20:45:55 -0700
Message-ID: <CAKFQuwbe4TSU_LxsrGxgcv4DfQq2x-UNoyKx-ABy36PhvbGCdg@mail.gmail.com>
Subject: Re: Errors when restoring backup created by pg_dumpall
To: PopeRigby <poperigby@mailbox.org>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f40d8906282d44c2"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwbe4TSU_LxsrGxgcv4DfQq2x-UNoyKx-ABy36PhvbGCdg%40mail.gmail.com>
Precedence: bulk

--000000000000f40d8906282d44c2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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)))::eart=
h
>>>>
>>>> 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 =E2=80=9Cearth=E2=80=9D is not schema qualified leads me t=
o 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=E2=80=A6that leaves finding the places in the text the lack the sch=
ema
> 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.

--000000000000f40d8906282d44c2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, November 30, 2024, PopeRigby &lt;<a href=3D"mailto:poperigby@m=
ailbox.org">poperigby@mailbox.org</a>&gt; wrote:<br><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <div>On 11/30/24 18:41, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type=3D"cite">
     =20
      On Saturday, November 30, 2024, PopeRigby &lt;<a href=3D"mailto:poper=
igby@mailbox.org" target=3D"_blank">poperigby@mailbox.org</a>&gt;
      wrote:<br>
      <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-l=
eft:1px #ccc solid;padding-left:1ex">
        <div>
          <div>On 11/30/24 17:27, David G. Johnston wrote:<br>
          </div>
          <blockquote type=3D"cite"> On Saturday, November 30, 2024,
            PopeRigby &lt;<a href=3D"mailto:poperigby@mailbox.org" target=
=3D"_blank">poperigby@mailbox.org</a>&gt;
            wrote:<br>
            <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;bo=
rder-left:1px #ccc solid;padding-left:1ex">On
              11/29/24 17:47, Adrian Klaver wrote:<br>
              <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;=
border-left:1px #ccc solid;padding-left:1ex">
                On 11/29/24 17:34, PopeRigby wrote:<br>
                <br>
                psql:all.sql:4104: ERROR:=C2=A0 type &quot;earth&quot; does=
 not exist<br>
                LINE 1: ...ians($1))*sin(radians($2)))<wbr>,earth()*sin(rad=
ians($1)))::ea<wbr>rth<br>
                <br>
                QUERY:=C2=A0 SELECT cube(cube(cube(earth()*cos(rad<wbr>ians=
($1))*cos(radians($2))),ea<wbr>rth()*cos(radians($1))*sin(rad<wbr>ians($2))=
),earth()*sin(radians<wbr>($1)))::earth<br>
                CONTEXT:=C2=A0 SQL function &quot;ll_to_earth&quot; during =
inlining<br>
                =C2=A0The earthdistance module is even getting added betwee=
n
                the table with the earth type is added, so shouldn&#39;t
                there be no problem?<br>
              </blockquote>
            </blockquote>
            <div><br>
            </div>
            <div>The fact that =E2=80=9Cearth=E2=80=9D is not schema qualif=
ied leads me
              to suspect you are getting bit by safe search_path
              environment rules.</div>
            <div><br>
            </div>
            <div>David J.=C2=A0</div>
          </blockquote>
          <p>Ah. How can I fix that?<br>
          </p>
        </div>
      </blockquote>
      <div>Since you are past the point of fixing the source to produce
        valid dumps=E2=80=A6that leaves finding the places in the text the =
lack
        the schema qualification and manually adding them in.</div>
      <div><br>
      </div>
      <div>David J.</div>
      <div><br>
      </div>
    </blockquote>
    <p>Oh also, it&#39;s the schema is specified as public on this line:
<a href=3D"https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b=
49#file-redacted_all-sql-L4111" target=3D"_blank">https://gist.github.com/<=
wbr>poperigby/<wbr>fcb59eb6c22c6051800e06a0ec482b<wbr>49#file-redacted_all-=
sql-L4111</a><br>
      <br>
      Why is it not finding it? I queried public and earth was in there.<br=
>
    </p></div></blockquote><div><br></div><div>Ok, so the error is not eman=
ating from your code but rather the body of the ll_to_earth function define=
d in the earthdistance extension.</div><div><br></div><div>David J.</div><d=
iv><br></div>

--000000000000f40d8906282d44c2--