public inbox for [email protected]help / color / mirror / Atom feed
Re: Errors when restoring backup created by pg_dumpall 5+ messages / 3 participants [nested] [flat]
* Re: Errors when restoring backup created by pg_dumpall @ 2024-12-05 23:04 Ron Johnson <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Ron Johnson @ 2024-12-05 23:04 UTC (permalink / raw) To: pgsql-general On Thu, Dec 5, 2024 at 5:32 PM PopeRigby <[email protected]> wrote: > On 12/1/24 13:55, Tom Lane wrote: > > Adrian Klaver <[email protected]> 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! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall @ 2024-12-05 23:23 David G. Johnston <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: David G. Johnston @ 2024-12-05 23:23 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <[email protected]> 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall @ 2024-12-06 02:44 Ron Johnson <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Ron Johnson @ 2024-12-06 02:44 UTC (permalink / raw) To: pgsql-general On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston <[email protected]> wrote: > On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <[email protected]> > 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! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall @ 2024-12-06 03:48 Adrian Klaver <[email protected]> parent: Ron Johnson <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2024-12-06 03:48 UTC (permalink / raw) To: Ron Johnson <[email protected]>; pgsql-general On 12/5/24 18:44, Ron Johnson wrote: > On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston > <[email protected] <mailto:[email protected]>> wrote: > > On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <[email protected] > <mailto:[email protected]>> 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 [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall @ 2024-12-06 05:06 David G. Johnston <[email protected]> parent: Ron Johnson <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: David G. Johnston @ 2024-12-06 05:06 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general On Thursday, December 5, 2024, Ron Johnson <[email protected]> wrote: > On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston < > [email protected]> wrote: > >> On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson <[email protected]> >> 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-12-06 05:06 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-12-05 23:04 Re: Errors when restoring backup created by pg_dumpall Ron Johnson <[email protected]> 2024-12-05 23:23 ` David G. Johnston <[email protected]> 2024-12-06 02:44 ` Ron Johnson <[email protected]> 2024-12-06 03:48 ` Adrian Klaver <[email protected]> 2024-12-06 05:06 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox