Thread: Errors when restoring backup created by pg_dumpall
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
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!
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
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?
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)))::ea rth
QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))), earth()*cos(radians($1))*sin(r adians($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.
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)))::ea rth
QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))), earth()*cos(radians($1))*sin(r adians($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.
Ah. How can I fix that?
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)))::ea rth
QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),ea rth()*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.
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)))::ea rth
QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),ea rth()*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 boy. How can I prevent this from happening again?
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
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)))::ea rth
QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),ea rth()*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/fcb59eb6c22c6051800e06a0ec482b49#file-redacted_all-sql-L4111
Why is it not finding it? I queried public and earth was in there.
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:
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?
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)))::ea rth
QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),ea rth()*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.
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)))::ea rth
QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),ea rth()*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.
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.
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.
"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
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
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
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
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
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
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
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?
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
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.
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.
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
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!
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.
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!
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
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.
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?
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.
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?
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.
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 :)
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
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!
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
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.
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
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
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!
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.
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!
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
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
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
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?
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
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!