Horton

Thread

Commits

  1. contrib/earthdistance: Use SQL-standard function bodies.

  1. Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-11-30T01:34:39Z

    My HDD recently failed so I'm trying to restore my backup, but I'm 
running into some errors.

I've been using a systemd service that periodically backs up my cluster 
with pg_dumpall, and I'm using this command to restore:

sudo psql -f backup.sql postgres

I'm getting this output: 
https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea

This is my (redacted) database dump: 
https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49

  2. Re: Errors when restoring backup created by pg_dumpall

    Ron <ronljohnsonjr@gmail.com> — 2024-11-30T01:44:46Z

    On Fri, Nov 29, 2024 at 8:35 PM PopeRigby <poperigby@mailbox.org> wrote:

> My HDD recently failed so I'm trying to restore my backup, but I'm
> running into some errors.
>
> I've been using a systemd service that periodically backs up my cluster
> with pg_dumpall, and I'm using this command to restore:
>
> sudo psql -f backup.sql postgres
>
> I'm getting this output:
> https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea
>
> This is my (redacted) database dump:
> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49
>

Have you installed whatever software provides extensions like cube, vector
and earthdistance?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  3. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-11-30T01:47:55Z

    On 11/29/24 17:34, PopeRigby wrote:
> My HDD recently failed so I'm trying to restore my backup, but I'm 
> running into some errors.
> 
> I've been using a systemd service that periodically backs up my cluster 
> with pg_dumpall, and I'm using this command to restore:
> 
> sudo psql -f backup.sql postgres
> 
> I'm getting this output: 
> https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea


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

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
);


Looks like an extension or extensions where not installed before the 
restore was done.


> 
> This is my (redacted) database dump: 
> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  4. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-01T01:17:31Z

    On 11/29/24 17:47, Adrian Klaver wrote:
> On 11/29/24 17:34, PopeRigby wrote:
>> My HDD recently failed so I'm trying to restore my backup, but I'm 
>> running into some errors.
>>
>> I've been using a systemd service that periodically backs up my 
>> cluster with pg_dumpall, and I'm using this command to restore:
>>
>> sudo psql -f backup.sql postgres
>>
>> I'm getting this output: 
>> https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea
>
>
> 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
>
> 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
> );
>
>
> Looks like an extension or extensions where not installed before the 
> restore was done.
>
>
>>
>> This is my (redacted) database dump: 
>> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49
>>
>>
>
Weird, I have all the same software that was installed before I 
restored, as I'm using NixOS. I'm guessing the earth type is provided by 
earthdistance, and in the SQL script it's able to successfully install 
cube, vector, and earthdistance. I think earthdistance and cube are 
actually built-in modules, right?

I ran the following commands, and earth is even one of the listed types:

postgres=# CREATE EXTENSION earthdistance;
CREATE EXTENSION
postgres=# SELECT t.typname
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_catalog.pg_extension e ON e.extnamespace = n.oid
WHERE e.extname = 'earthdistance';
  typname
---------
  _cube
  _earth
  cube
  earth
(4 rows)

The earthdistance module is even getting added between the table with 
the earth type is added, so shouldn't there be no problem?

  5. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-01T01:27:26Z

    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(radia
>> ns($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.

  6. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-01T02:11:18Z

    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?

  7. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-01T02:41:27Z

    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(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.

  8. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-01T03:26:51Z

    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 boy. How can I prevent this from happening again?

  9. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-01T03:36:03Z

    On 11/30/24 19:26, PopeRigby 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 boy. How can I prevent this from happening again?
> 

In future schema qualify all references.

For now in the dump file you could search for

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

and set to

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





-- 
Adrian Klaver
adrian.klaver@aklaver.com

  10. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-01T03:36:46Z

    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.

  11. Re: Errors when restoring backup created by pg_dumpall

    Marco Torres <mtors25@gmail.com> — 2024-12-01T03:45:05Z

    CREATE EXTENSION cube;

I do not know if you might need this one as well. I am assuming that you
are working on a gist server.

CREATE EXTENSION earthdistance;


I am assuming you are working with a gist server. This ought to be useful.
https://gist.cs.berkeley.edu/pggist/

You might want to read this:
https://docs.gitlab.com/ee/install/postgresql_extensions.html

My advice is to go to google, then chat GPT if you do not get any good
feedback here. Hopefully, this will give you good leads.

On Sat, Nov 30, 2024, 8:27 PM 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 boy. How can I prevent this from happening again?
>

  12. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-01T03:45:55Z

    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(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.

  13. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-01T03:47:55Z

    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
>     <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.

  14. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-01T03:51:40Z

    On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> 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.

  15. Re: Errors when restoring backup created by pg_dumpall

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-01T03:58:13Z

    "David G. Johnston" <david.g.johnston@gmail.com> 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/3395418.1618352794@sss.pgh.pa.us

  16. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-01T20:05:03Z

    On 11/30/24 19:58, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> 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/3395418.1618352794@sss.pgh.pa.us

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

  17. Re: Errors when restoring backup created by pg_dumpall

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-01T20:15:09Z

    PopeRigby <poperigby@mailbox.org> writes:
> I've applied the following patch to postgres:
> https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

Cool.  You did actually install the new scripts into your target
installation, right?

> 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

Hmmm ... a pg_dumpall output script shouldn't really contain that
function body directly; it should just say "CREATE EXTENSION
earthdistance".  Is it possible that this database is so old that
it contains a pre-extension (pre-9.1) version of earthdistance?

			regards, tom lane

  18. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-01T21:08:28Z

    On 12/1/24 12:05, PopeRigby wrote:
> On 11/30/24 19:58, Tom Lane wrote:
>> "David G. Johnston" <david.g.johnston@gmail.com> 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/3395418.1618352794@sss.pgh.pa.us
> 
> 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
adrian.klaver@aklaver.com

  19. Re: Errors when restoring backup created by pg_dumpall

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-01T21:14:18Z

    Adrian Klaver <adrian.klaver@aklaver.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.

			regards, tom lane

  20. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-01T21:36:36Z

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

  21. Re: Errors when restoring backup created by pg_dumpall

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-01T21:55:08Z

    Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 12/1/24 13:14, Tom Lane wrote:
>> 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
> );

Ah!  Then the failure occurs because we do a planning pass on the
GENERATED expression (I don't remember exactly why that's needed
during CREATE TABLE).  So maybe messing with the dump script's
search_path setting *would* be enough to get you past that.

Having said that, the CREATE should have been seeing the new-style
definition of ll_to_earth() if the 1.2 version of earthdistance
was correctly installed.

			regards, tom lane

  22. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-02T23:21:04Z

    On 12/1/24 12:15, Tom Lane wrote:
> Cool.  You did actually install the new scripts into your target
> installation, right?

Oh, is applying the patch and rebuilding PostgreSQL not enough?

  23. Re: Errors when restoring backup created by pg_dumpall

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-03T01:17:21Z

    PopeRigby <poperigby@mailbox.org> writes:
> On 12/1/24 12:15, Tom Lane wrote:
>> Cool.  You did actually install the new scripts into your target
>> installation, right?

> Oh, is applying the patch and rebuilding PostgreSQL not enough?

Not unless you did "make install" in the contrib/earthdistance
directory (or something wider-scope that would invoke that).

			regards, tom lane

  24. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-04T05:13:42Z

    On 12/2/24 17:17, Tom Lane wrote:
> PopeRigby <poperigby@mailbox.org> writes:
>> On 12/1/24 12:15, Tom Lane wrote:
>>> Cool.  You did actually install the new scripts into your target
>>> installation, right?
>> Oh, is applying the patch and rebuilding PostgreSQL not enough?
> Not unless you did "make install" in the contrib/earthdistance
> directory (or something wider-scope that would invoke that).
>
> 			regards, tom lane

Well, I did actually compile PostgreSQL after applying the patch.

  25. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-05T22:32:11Z

    On 12/1/24 13:55, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 12/1/24 13:14, Tom Lane wrote:
>>> 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
>> );
> Ah!  Then the failure occurs because we do a planning pass on the
> GENERATED expression (I don't remember exactly why that's needed
> during CREATE TABLE).  So maybe messing with the dump script's
> search_path setting *would* be enough to get you past that.
>
> Having said that, the CREATE should have been seeing the new-style
> definition of ll_to_earth() if the 1.2 version of earthdistance
> was correctly installed.
>
> 			regards, tom lane

So, is there anything I can do to fix this particular backup? I'm kind 
of stuck here. There's also the issue with it for some reason failing to 
connect to the lldap database after it literally just created it. Some 
weird things going on.

  26. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-05T22:48:34Z

    On 12/5/24 14:32, PopeRigby wrote:
> On 12/1/24 13:55, Tom Lane wrote:
>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>> On 12/1/24 13:14, Tom Lane wrote:
>>>> 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
>>> );
>> Ah!  Then the failure occurs because we do a planning pass on the
>> GENERATED expression (I don't remember exactly why that's needed
>> during CREATE TABLE).  So maybe messing with the dump script's
>> search_path setting *would* be enough to get you past that.
>>
>> Having said that, the CREATE should have been seeing the new-style
>> definition of ll_to_earth() if the 1.2 version of earthdistance
>> was correctly installed.
>>
>>             regards, tom lane
> 
> So, is there anything I can do to fix this particular backup? I'm kind 
> of stuck here. There's also the issue with it for some reason failing to 
> connect to the lldap database after it literally just created it. Some 
> weird things going on.
> 

In the pg_dumpall sql script did you change:

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

to

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

?


Show us the connection error you got for the lldap database.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  27. Re: Errors when restoring backup created by pg_dumpall

    Ron <ronljohnsonjr@gmail.com> — 2024-12-05T23:04:31Z

    On Thu, Dec 5, 2024 at 5:32 PM PopeRigby <poperigby@mailbox.org> wrote:

> On 12/1/24 13:55, Tom Lane wrote:
> > Adrian Klaver <adrian.klaver@aklaver.com> writes:
> >> On 12/1/24 13:14, Tom Lane wrote:
> >>> 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
> >> );
> > Ah!  Then the failure occurs because we do a planning pass on the
> > GENERATED expression (I don't remember exactly why that's needed
> > during CREATE TABLE).  So maybe messing with the dump script's
> > search_path setting *would* be enough to get you past that.
> >
> > Having said that, the CREATE should have been seeing the new-style
> > definition of ll_to_earth() if the 1.2 version of earthdistance
> > was correctly installed.
> >
> >                       regards, tom lane
>
> So, is there anything I can do to fix this particular backup? I'm kind
> of stuck here. There's also the issue with it for some reason failing to
> connect to the lldap database after it literally just created it. Some
> weird things going on.
>

Another alternative is to open the .sql file in Notepad++, then add
"public." before all the unqualified "earth" and "ll_to_earth" references.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  28. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-05T23:23:49Z

    On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> Another alternative is to open the .sql file in Notepad++, then add
> "public." before all the unqualified "earth" and "ll_to_earth" references.
>

And as discussed there are none in that file because those references are
within an extension's objects and only create extension appears in the file.

David J.

  29. Re: Errors when restoring backup created by pg_dumpall

    Ron <ronljohnsonjr@gmail.com> — 2024-12-06T02:44:07Z

    On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> Another alternative is to open the .sql file in Notepad++, then add
>> "public." before all the unqualified "earth" and "ll_to_earth" references.
>>
>
> And as discussed there are none in that file because those references are
> within an extension's objects and only create extension appears in the file.
>

Then why would changing search_path work?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  30. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-06T03:48:37Z

    On 12/5/24 18:44, Ron Johnson wrote:
> On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <ronljohnsonjr@gmail.com
>     <mailto:ronljohnsonjr@gmail.com>> wrote:
> 
>         Another alternative is to open the .sql file in Notepad++, then
>         add "public." before all the unqualified "earth" and
>         "ll_to_earth" references.
> 
> 
>     And as discussed there are none in that file because those
>     references are within an extension's objects and only create
>     extension appears in the file.
> 
> 
> Then why would changing search_path work?

Because that is set for each database before the objects in that 
database are created. That means anything that is created subsequently 
will be have access to all other objects created in the path. This would 
include objects created indirectly from an extension as well as objects 
that include non-schema qualified names.

> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  31. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-06T05:06:42Z

    On Thursday, December 5, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <ronljohnsonjr@gmail.com>
>> wrote:
>>
>>> Another alternative is to open the .sql file in Notepad++, then add
>>> "public." before all the unqualified "earth" and "ll_to_earth" references.
>>>
>>
>> And as discussed there are none in that file because those references are
>> within an extension's objects and only create extension appears in the file.
>>
>
> Then why would changing search_path work?
>
>
Because (I presume) function inlining during execution of create table
keeps the search_path of the session executing create table which will then
result in the parser resolving the unqualified “earth” function name to the
one existing in the public schema when looking through the session’s
search_path.

David J.

  32. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-07T19:25:29Z

    On 12/5/24 14:48, Adrian Klaver wrote:
> On 12/5/24 14:32, PopeRigby wrote:
>> On 12/1/24 13:55, Tom Lane wrote:
>>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>>> On 12/1/24 13:14, Tom Lane wrote:
>>>>> 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
>>>> );
>>> Ah!  Then the failure occurs because we do a planning pass on the
>>> GENERATED expression (I don't remember exactly why that's needed
>>> during CREATE TABLE).  So maybe messing with the dump script's
>>> search_path setting *would* be enough to get you past that.
>>>
>>> Having said that, the CREATE should have been seeing the new-style
>>> definition of ll_to_earth() if the 1.2 version of earthdistance
>>> was correctly installed.
>>>
>>>             regards, tom lane
>>
>> So, is there anything I can do to fix this particular backup? I'm 
>> kind of stuck here. There's also the issue with it for some reason 
>> failing to connect to the lldap database after it literally just 
>> created it. Some weird things going on.
>>
>
> In the pg_dumpall sql script did you change:
>
> SELECT pg_catalog.set_config('search_path', '', false);
>
> to
>
> SELECT pg_catalog.set_config('search_path', 'public', false);
>
> ?
>
>
> Show us the connection error you got for the lldap database.
>
It actually looks like setting those all to have public fixed all the 
errors, including the one with lldap. So, how can I get it to not put 
public there automatically for next time?

  33. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-07T19:58:23Z

    On Sat, Dec 7, 2024 at 12:25 PM PopeRigby <poperigby@mailbox.org> wrote:

>
> It actually looks like setting those all to have public fixed all the
> errors, including the one with lldap. So, how can I get it to not put
> public there automatically for next time?
>
>
I assume you mean "get it to put public there" (i.e., the "not" is a typo)

You cannot.  The security team has decided to not permit an opt-in bypass
of the lock-downs implemented to fix CVE-2018-1058.

Your only real choice at the moment is to replace the function call in the
generated expression with a custom function and in that custom function's
create function command attach a "set search_path to public" clause.  That
will prevent inlining and also ensure the public schema is in the
search_path when executing the public.ll_to_earth function call.  With that
in place the empty search_path in the dump file will no longer matter.

David J.

  34. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-09T22:14:04Z

    On 12/7/24 11:58, David G. Johnston wrote:
> On Sat, Dec 7, 2024 at 12:25 PM PopeRigby <poperigby@mailbox.org> wrote:
>
>
>     It actually looks like setting those all to have public fixed all the
>     errors, including the one with lldap. So, how can I get it to not put
>     public there automatically for next time?
>
>
> I assume you mean "get it to put public there" (i.e., the "not" is a typo)
>
> You cannot. The security team has decided to not permit an opt-in 
> bypass of the lock-downs implemented to fix CVE-2018-1058.
>
> Your only real choice at the moment is to replace the function call in 
> the generated expression with a custom function and in that custom 
> function's create function command attach a "set search_path to 
> public" clause.  That will prevent inlining and also ensure the public 
> schema is in the search_path when executing the public.ll_to_earth 
> function call.  With that in place the empty search_path in the dump 
> file will no longer matter.
>
> David J.
>
Yeah, that was a typo. It seems weird that this behavior would be broken 
by default though, is there anything that could fix it upstream?

  35. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-09T22:31:52Z

    On Mon, Dec 9, 2024 at 3:14 PM PopeRigby <poperigby@mailbox.org> wrote:

> On 12/7/24 11:58, David G. Johnston wrote:
>
> On Sat, Dec 7, 2024 at 12:25 PM PopeRigby <poperigby@mailbox.org> wrote:
>
>>
>> It actually looks like setting those all to have public fixed all the
>> errors, including the one with lldap. So, how can I get it to not put
>> public there automatically for next time?
>>
>>
> I assume you mean "get it to put public there" (i.e., the "not" is a typo)
>
> You cannot.  The security team has decided to not permit an opt-in bypass
> of the lock-downs implemented to fix CVE-2018-1058.
>
> Your only real choice at the moment is to replace the function call in the
> generated expression with a custom function and in that custom function's
> create function command attach a "set search_path to public" clause.  That
> will prevent inlining and also ensure the public schema is in the
> search_path when executing the public.ll_to_earth function call.  With that
> in place the empty search_path in the dump file will no longer matter.
>
> Yeah, that was a typo. It seems weird that this behavior would be broken
> by default though, is there anything that could fix it upstream?
>

You saw and tried the work being done "upstream" to fix the situation.
It's a big knot in the system and it isn't easy (or highly motivated) to
untangle unfortunately...

David J.

  36. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-09T22:34:27Z

    On 12/9/24 14:31, David G. Johnston wrote:
> On Mon, Dec 9, 2024 at 3:14 PM PopeRigby <poperigby@mailbox.org> wrote:
>
>     On 12/7/24 11:58, David G. Johnston wrote:
>>     On Sat, Dec 7, 2024 at 12:25 PM PopeRigby <poperigby@mailbox.org>
>>     wrote:
>>
>>
>>         It actually looks like setting those all to have public fixed
>>         all the
>>         errors, including the one with lldap. So, how can I get it to
>>         not put
>>         public there automatically for next time?
>>
>>
>>     I assume you mean "get it to put public there" (i.e., the "not"
>>     is a typo)
>>
>>     You cannot.  The security team has decided to not permit an
>>     opt-in bypass of the lock-downs implemented to fix CVE-2018-1058.
>>
>>     Your only real choice at the moment is to replace the function
>>     call in the generated expression with a custom function and in
>>     that custom function's create function command attach a "set
>>     search_path to public" clause.  That will prevent inlining and
>>     also ensure the public schema is in the search_path when
>>     executing the public.ll_to_earth function call.  With that in
>>     place the empty search_path in the dump file will no longer matter.
>>
>     Yeah, that was a typo. It seems weird that this behavior would be
>     broken by default though, is there anything that could fix it
>     upstream?
>
>
> You saw and tried the work being done "upstream" to fix the situation. 
> It's a big knot in the system and it isn't easy (or highly motivated) 
> to untangle unfortunately...
>
> David J.
>
Understood. Well, at least it was a fairly easy fix. Thanks for the help :)

  37. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-09T22:47:07Z

    On 12/9/24 14:14, PopeRigby wrote:
> On 12/7/24 11:58, David G. Johnston wrote:
>> On Sat, Dec 7, 2024 at 12:25 PM PopeRigby <poperigby@mailbox.org> wrote:
>>
>>
>>     It actually looks like setting those all to have public fixed all the
>>     errors, including the one with lldap. So, how can I get it to not put
>>     public there automatically for next time?
>>
>>
>> I assume you mean "get it to put public there" (i.e., the "not" is a typo)
>>
>> You cannot. The security team has decided to not permit an opt-in 
>> bypass of the lock-downs implemented to fix CVE-2018-1058.
>>
>> Your only real choice at the moment is to replace the function call in 
>> the generated expression with a custom function and in that custom 
>> function's create function command attach a "set search_path to 
>> public" clause.  That will prevent inlining and also ensure the public 
>> schema is in the search_path when executing the public.ll_to_earth 
>> function call.  With that in place the empty search_path in the dump 
>> file will no longer matter.
>>
>> David J.
>>
> Yeah, that was a typo. It seems weird that this behavior would be broken 
> by default though, is there anything that could fix it upstream?
> 

You could file an issue here:

https://www.postgresql.org/account/login/?next=/account/submitbug/

Ask if the developers could use the mechanisms available here:

https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION

to schema qualify the objects in the extension.

Not sure if that will fly or not, but it is worth a shot.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  38. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-09T22:49:14Z

    On 12/9/24 14:47, Adrian Klaver wrote:
> On 12/9/24 14:14, PopeRigby wrote:
>> On 12/7/24 11:58, David G. Johnston wrote:
>>> On Sat, Dec 7, 2024 at 12:25 PM PopeRigby <poperigby@mailbox.org> 
>>> wrote:
>>>
>>>
>>>     It actually looks like setting those all to have public fixed 
>>> all the
>>>     errors, including the one with lldap. So, how can I get it to 
>>> not put
>>>     public there automatically for next time?
>>>
>>>
>>> I assume you mean "get it to put public there" (i.e., the "not" is a 
>>> typo)
>>>
>>> You cannot. The security team has decided to not permit an opt-in 
>>> bypass of the lock-downs implemented to fix CVE-2018-1058.
>>>
>>> Your only real choice at the moment is to replace the function call 
>>> in the generated expression with a custom function and in that 
>>> custom function's create function command attach a "set search_path 
>>> to public" clause.  That will prevent inlining and also ensure the 
>>> public schema is in the search_path when executing the 
>>> public.ll_to_earth function call.  With that in place the empty 
>>> search_path in the dump file will no longer matter.
>>>
>>> David J.
>>>
>> Yeah, that was a typo. It seems weird that this behavior would be 
>> broken by default though, is there anything that could fix it upstream?
>>
>
> You could file an issue here:
>
> https://www.postgresql.org/account/login/?next=/account/submitbug/
>
> Ask if the developers could use the mechanisms available here:
>
> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION 
>
>
> to schema qualify the objects in the extension.
>
> Not sure if that will fly or not, but it is worth a shot.
>
Will do!

  39. Re: Errors when restoring backup created by pg_dumpall

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-09T23:23:26Z

    Adrian Klaver <adrian.klaver@aklaver.com> writes:
> You could file an issue here:
> https://www.postgresql.org/account/login/?next=/account/submitbug/
> Ask if the developers could use the mechanisms available here:
> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION

This wouldn't really move the needle, since (a) there is not some
magic group of people that will become involved who aren't already
and (b) the requested fix seems exactly the same as the work
already in progress at [1].

What would help is to figure out why the proposed patch didn't
seem to work for you.  I continue to suspect that you didn't
really install the updated extension, but it's unclear.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/3395418.1618352794@sss.pgh.pa.us

  40. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-09T23:30:40Z

    On 12/9/24 15:23, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> You could file an issue here:
>> https://www.postgresql.org/account/login/?next=/account/submitbug/
>> Ask if the developers could use the mechanisms available here:
>> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
> This wouldn't really move the needle, since (a) there is not some
> magic group of people that will become involved who aren't already
> and (b) the requested fix seems exactly the same as the work
> already in progress at [1].
>
> What would help is to figure out why the proposed patch didn't
> seem to work for you.  I continue to suspect that you didn't
> really install the updated extension, but it's unclear.
>
> 			regards, tom lane
>
> [1] https://www.postgresql.org/message-id/flat/3395418.1618352794@sss.pgh.pa.us

I compiled and installed PostgreSQL with the patch, and restarted it, so 
unless there are other steps I'm not sure what I could have done wrong.

  41. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-10T00:07:53Z

    On 12/9/24 15:23, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> You could file an issue here:
>> https://www.postgresql.org/account/login/?next=/account/submitbug/
>> Ask if the developers could use the mechanisms available here:
>> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
> 
> This wouldn't really move the needle, since (a) there is not some
> magic group of people that will become involved who aren't already
> and (b) the requested fix seems exactly the same as the work
> already in progress at [1].

I made the suggestion to the OP, my mistake. Somewhere I missed that 
work was being done on this.

> 
> What would help is to figure out why the proposed patch didn't
> seem to work for you.  I continue to suspect that you didn't
> really install the updated extension, but it's unclea

Are you referring to this?:

https://www.postgresql.org/message-id/3316564.aeNJFYEL58@aivenlaptop

> 
> 			regards, tom lane
> 
> [1] https://www.postgresql.org/message-id/flat/3395418.1618352794@sss.pgh.pa.us

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  42. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-10T00:31:49Z

    On 12/9/24 15:30, PopeRigby wrote:
> On 12/9/24 15:23, Tom Lane wrote:
>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>> You could file an issue here:
>>> https://www.postgresql.org/account/login/?next=/account/submitbug/
>>> Ask if the developers could use the mechanisms available here:
>>> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
>> This wouldn't really move the needle, since (a) there is not some
>> magic group of people that will become involved who aren't already
>> and (b) the requested fix seems exactly the same as the work
>> already in progress at [1].
>>
>> What would help is to figure out why the proposed patch didn't
>> seem to work for you.  I continue to suspect that you didn't
>> really install the updated extension, but it's unclear.
>>
>>             regards, tom lane
>>
>> [1] 
>> https://www.postgresql.org/message-id/flat/3395418.1618352794@sss.pgh.pa.us
> 
> I compiled and installed PostgreSQL with the patch, and restarted it, so 
> unless there are other steps I'm not sure what I could have done wrong.
> 


What was the source of the patch?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  43. Re: Errors when restoring backup created by pg_dumpall

    Ron <ronljohnsonjr@gmail.com> — 2024-12-10T04:02:59Z

    On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
[snip]

> In future schema qualify all references.
>
> For now in the dump file you could search for
>
> SELECT pg_catalog.set_config('search_path', '', false);
>
> and set to
>
> SELECT pg_catalog.set_config('search_path', 'public', false);
>

What if this had been a pg_dump --format={custom,directory} backup?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  44. Re: Errors when restoring backup created by pg_dumpall

    David G. Johnston <david.g.johnston@gmail.com> — 2024-12-10T04:24:17Z

    On Monday, December 9, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
> [snip]
>
>> In future schema qualify all references.
>>
>> For now in the dump file you could search for
>>
>> SELECT pg_catalog.set_config('search_path', '', false);
>>
>> and set to
>>
>> SELECT pg_catalog.set_config('search_path', 'public', false);
>>
>
> What if this had been a pg_dump --format={custom,directory} backup?
>

pg_restore has a mode where it can dump out SQL to a script instead of
directly restoring to the database.

David J.

  45. Re: Errors when restoring backup created by pg_dumpall

    Ron <ronljohnsonjr@gmail.com> — 2024-12-10T04:54:25Z

    On Mon, Dec 9, 2024 at 11:24 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Monday, December 9, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
>> On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
>> wrote:
>> [snip]
>>
>>> In future schema qualify all references.
>>>
>>> For now in the dump file you could search for
>>>
>>> SELECT pg_catalog.set_config('search_path', '', false);
>>>
>>> and set to
>>>
>>> SELECT pg_catalog.set_config('search_path', 'public', false);
>>>
>>
>> What if this had been a pg_dump --format={custom,directory} backup?
>>
>
> pg_restore has a mode where it can dump out SQL to a script instead of
> directly restoring to the database.
>

That Would Be Very, Very Bad if this were a database big enough to have
required a multi-threaded dump.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  46. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-10T05:31:15Z

    On 12/9/24 20:54, Ron Johnson wrote:
> On Mon, Dec 9, 2024 at 11:24 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Monday, December 9, 2024, Ron Johnson <ronljohnsonjr@gmail.com
>     <mailto:ronljohnsonjr@gmail.com>> wrote:
> 
>         On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>         wrote:
>         [snip]
> 
>             In future schema qualify all references.
> 
>             For now in the dump file you could search for
> 
>             SELECT pg_catalog.set_config('search_path', '', false);
> 
>             and set to
> 
>             SELECT pg_catalog.set_config('search_path', 'public', false);
> 
> 
>         What if this had been a pg_dump --format={custom,directory} backup?
> 
> 
>     pg_restore has a mode where it can dump out SQL to a script instead
>     of directly restoring to the database.
> 
> 
> That Would Be Very, Very Bad if this were a database big enough to have 
> required a multi-threaded dump.

 From the original post:

https://www.postgresql.org/message-id/6a6439f1-8039-44e2-8fb9-59028f7f2014%40mailbox.org

"My HDD recently failed so I'm trying to restore my backup, but I'm
running into some errors."

There really was no choice.

Not tested but in the pg_restore case I could see at least trying:

1) pg_restore -s  -f schema_definitions.sql  custom_format_file

2) Then making the change in the search_path in schema_definitions.sql 
and then load the schema in the database using psql

3) Then pg_restore -a -j <number_of_jobs> custom_format_file.

> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  47. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-11T03:05:53Z

    On 12/9/24 16:31, Adrian Klaver wrote:
> On 12/9/24 15:30, PopeRigby wrote:
>> On 12/9/24 15:23, Tom Lane wrote:
>>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>>> You could file an issue here:
>>>> https://www.postgresql.org/account/login/?next=/account/submitbug/
>>>> Ask if the developers could use the mechanisms available here:
>>>> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION 
>>>>
>>> This wouldn't really move the needle, since (a) there is not some
>>> magic group of people that will become involved who aren't already
>>> and (b) the requested fix seems exactly the same as the work
>>> already in progress at [1].
>>>
>>> What would help is to figure out why the proposed patch didn't
>>> seem to work for you.  I continue to suspect that you didn't
>>> really install the updated extension, but it's unclear.
>>>
>>>             regards, tom lane
>>>
>>> [1] 
>>> https://www.postgresql.org/message-id/flat/3395418.1618352794@sss.pgh.pa.us
>>
>> I compiled and installed PostgreSQL with the patch, and restarted it, 
>> so unless there are other steps I'm not sure what I could have done 
>> wrong.
>>
>
>
> What was the source of the patch?
>
This: 
https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

  48. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-11T05:22:01Z

    On 12/10/24 19:05, PopeRigby wrote:
> On 12/9/24 16:31, Adrian Klaver wrote:
>> On 12/9/24 15:30, PopeRigby wrote:
>>> On 12/9/24 15:23, Tom Lane wrote:
>>>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>>>> You could file an issue here:
>>>>> https://www.postgresql.org/account/login/?next=/account/submitbug/
>>>>> Ask if the developers could use the mechanisms available here:
>>>>> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
>>>> This wouldn't really move the needle, since (a) there is not some
>>>> magic group of people that will become involved who aren't already
>>>> and (b) the requested fix seems exactly the same as the work
>>>> already in progress at [1].
>>>>
>>>> What would help is to figure out why the proposed patch didn't
>>>> seem to work for you.  I continue to suspect that you didn't
>>>> really install the updated extension, but it's unclear.
>>>>
>>>>             regards, tom lane
>>>>
>>>> [1] 
>>>> https://www.postgresql.org/message-id/flat/3395418.1618352794@sss.pgh.pa.us
>>>
>>> I compiled and installed PostgreSQL with the patch, and restarted it, 
>>> so unless there are other steps I'm not sure what I could have done 
>>> wrong.
>>>
>>
>>
>> What was the source of the patch?
>>
> This: 
> https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch
> 

If I am following correctly I believe you need this one:

https://www.postgresql.org/message-id/attachment/166859/v2-0002-Use-new-style-SQL-function-in-earthdistance-exten.patch

It has the changes that include the schema qualifications. The one you 
used does not have those modifications.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  49. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-13T06:19:11Z

    On 12/10/24 21:22, Adrian Klaver wrote:
> On 12/10/24 19:05, PopeRigby wrote:
>> On 12/9/24 16:31, Adrian Klaver wrote:
>>> On 12/9/24 15:30, PopeRigby wrote:
>>>> On 12/9/24 15:23, Tom Lane wrote:
>>>>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>>>>> You could file an issue here:
>>>>>> https://www.postgresql.org/account/login/?next=/account/submitbug/
>>>>>> Ask if the developers could use the mechanisms available here:
>>>>>> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION 
>>>>>>
>>>>> This wouldn't really move the needle, since (a) there is not some
>>>>> magic group of people that will become involved who aren't already
>>>>> and (b) the requested fix seems exactly the same as the work
>>>>> already in progress at [1].
>>>>>
>>>>> What would help is to figure out why the proposed patch didn't
>>>>> seem to work for you.  I continue to suspect that you didn't
>>>>> really install the updated extension, but it's unclear.
>>>>>
>>>>>             regards, tom lane
>>>>>
>>>>> [1] 
>>>>> https://www.postgresql.org/message-id/flat/3395418.1618352794@sss.pgh.pa.us
>>>>
>>>> I compiled and installed PostgreSQL with the patch, and restarted 
>>>> it, so unless there are other steps I'm not sure what I could have 
>>>> done wrong.
>>>>
>>>
>>>
>>> What was the source of the patch?
>>>
>> This: 
>> https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch
>>
>
> If I am following correctly I believe you need this one:
>
> https://www.postgresql.org/message-id/attachment/166859/v2-0002-Use-new-style-SQL-function-in-earthdistance-exten.patch 
>
>
> It has the changes that include the schema qualifications. The one you 
> used does not have those modifications.
>
Beautiful. How can I follow this so I know when it gets upstream?

  50. Re: Errors when restoring backup created by pg_dumpall

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-12-13T16:46:56Z

    On 12/12/24 22:19, PopeRigby wrote:
> On 12/10/24 21:22, Adrian Klaver wrote:
>> On 12/10/24 19:05, PopeRigby wrote:

>>
>> If I am following correctly I believe you need this one:
>>
>> https://www.postgresql.org/message-id/attachment/166859/v2-0002-Use-new-style-SQL-function-in-earthdistance-exten.patch
>>
>> It has the changes that include the schema qualifications. The one you 
>> used does not have those modifications.
>>
> Beautiful. How can I follow this so I know when it gets upstream?
> 


You can check here:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/earthdistance;h=282f5a80b204a488b5c8ec6d24eaaab8a714bfaf;hb=HEAD

and see when the earthdistance--1.1--1.2.sql and earthdistance--1.2.sql
scripts show up.

Though a formal release won't happen until the dates listed here:

https://www.postgresql.org/developer/roadmap/

with nearest one being February 13th, 2025.

FYI, these are target dates and not set in stone.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  51. Re: Errors when restoring backup created by pg_dumpall

    PopeRigby <poperigby@mailbox.org> — 2024-12-13T22:00:16Z

    On 12/13/24 08:46, Adrian Klaver wrote:
> On 12/12/24 22:19, PopeRigby wrote:
>> On 12/10/24 21:22, Adrian Klaver wrote:
>>> On 12/10/24 19:05, PopeRigby wrote:
>
>>>
>>> If I am following correctly I believe you need this one:
>>>
>>> https://www.postgresql.org/message-id/attachment/166859/v2-0002-Use-new-style-SQL-function-in-earthdistance-exten.patch 
>>>
>>>
>>> It has the changes that include the schema qualifications. The one 
>>> you used does not have those modifications.
>>>
>> Beautiful. How can I follow this so I know when it gets upstream?
>>
>
>
> You can check here:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/earthdistance;h=282f5a80b204a488b5c8ec6d24eaaab8a714bfaf;hb=HEAD 
>
>
> and see when the earthdistance--1.1--1.2.sql and earthdistance--1.2.sql
> scripts show up.
>
> Though a formal release won't happen until the dates listed here:
>
> https://www.postgresql.org/developer/roadmap/
>
> with nearest one being February 13th, 2025.
>
> FYI, these are target dates and not set in stone.
>
Thanks!