Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJQYC-002vFK-5w for pgsql-general@arkaria.postgresql.org; Fri, 06 Dec 2024 05:06:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tJQY9-009zLw-NK for pgsql-general@arkaria.postgresql.org; Fri, 06 Dec 2024 05:06:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJQY9-009zLo-AD for pgsql-general@lists.postgresql.org; Fri, 06 Dec 2024 05:06:46 +0000 Received: from mail-oa1-x2a.google.com ([2001:4860:4864:20::2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tJQY8-001IUJ-3T for pgsql-general@postgresql.org; Fri, 06 Dec 2024 05:06:45 +0000 Received: by mail-oa1-x2a.google.com with SMTP id 586e51a60fabf-29e70c9dc72so1004474fac.0 for ; Thu, 05 Dec 2024 21:06:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733461603; x=1734066403; darn=postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=r6TvsNKCmE9FWoM1M+Jffl2bX+dA5LLxEDBTlIKfxnw=; b=Obril32J1t2mwr0lu4WebFzerTLal/brTwyxDKQizv/YvN71iiJoYyV24L/zVAc8Dd mLohUv2VUAZ7LdRts3vPlPIOBSgK7rwGqGp0PwT3nSOovLFsvSdAk6JOLGCCzoZZrnNw WMFqYEPjMUYivdl+TD3Q6/W13vBESKzOwWWPEzIc756PwOyvVfPFJyy0dt6gB8tzbWku nifjQn66Zg+/0hJGAvOP8kulGwKxxF4vYocIoMmBPwIlZwbObiIeR+BBIkmaHSTOqfw5 S+uEkDOA4Y4ZqSNWjxkblBrFH/HBfnLA6/qqD6gloKs3hYZGFRLE5pqkm1eOcztjo8nK flJw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733461603; x=1734066403; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=r6TvsNKCmE9FWoM1M+Jffl2bX+dA5LLxEDBTlIKfxnw=; b=d8hQ7mE0LU/Pwt/dUevpoIvTZ1810UiPlHw/Bc2tvWeT+F614VrfxlRooAD3AXN4Pe VAPQ2Ej3ouWUMbXBo4wZgPNrWnEysXLiXuPlmAtEhGu6vt815G5SSNOZuXob8/BmwRwn meMJm6ZaN7vN+eu4c/zhiGBSY4hkjer0MnqkUPVSgQ3g2QgYxZ5Gf7gajxr1l8ZAYuMq QpvIztlFJxZqdYCzjV/4orNXZtMGy5Squ05Jpjq1/GWZFtLwRdetMjs91BpOY8d8Yibd lYFYEYbkptHwTJtxD4+X1ZXN7T+l68fPwCDgZnkW7GlE/7jyCe+CqN0G9q9182MqZBFj Ysag== X-Gm-Message-State: AOJu0YwCLDleEyKbzgJYShUne85SIG4j6TH9er2ctPsGzNoXOMuH26XP I9PirpcYKqc/GczwGbd1CTflutvnpCR4YEeJJrHk3jVQDPnUhwGyoTzbb3VSIjoP5qYvCe8ykh0 0bt11g2lYoIX2GPFKqPWfQIgHdTucLKL2 X-Gm-Gg: ASbGncvfKocPwh8sp9SoPUNZ5OvA1Arbc2wKQyLXF/9Mw650pudodEM62HecqxezBWO M+4EsLmx56yI8bBBOfKSE+sMobjlS2Rc= X-Google-Smtp-Source: AGHT+IEKYAQFCjLRU0MpjY3cjwpuAzJifZsGBrKx4p24EJHAP8ugSHzYKh2fd4YXqBc1lUo2x7xTeGNjquuz4F6sF1Y= X-Received: by 2002:a05:6870:14d6:b0:296:e491:b244 with SMTP id 586e51a60fabf-29f7352baeamr882267fac.32.1733461603132; Thu, 05 Dec 2024 21:06:43 -0800 (PST) MIME-Version: 1.0 Received: by 2002:a05:6802:3307:b0:577:9519:f64a with HTTP; Thu, 5 Dec 2024 21:06:42 -0800 (PST) In-Reply-To: References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org> <9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org> <41791b6d-aaf5-4fed-9cc3-e89bc49e8637@mailbox.org> <1257068.1733025493@sss.pgh.pa.us> <06425038-e012-4bac-aec1-d9541436f893@mailbox.org> <92405f98-7f0c-4442-8252-697352daefc1@aklaver.com> <1470486.1733087658@sss.pgh.pa.us> <4ab662d8-57cc-471f-8a58-cfd71d1cea22@aklaver.com> <1475512.1733090108@sss.pgh.pa.us> <19d5f2c7-1252-4442-accb-8aa2cb289ad0@mailbox.org> From: "David G. Johnston" Date: Thu, 5 Dec 2024 22:06:42 -0700 Message-ID: Subject: Re: Errors when restoring backup created by pg_dumpall To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000162f36062892fb89" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000162f36062892fb89 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, December 5, 2024, Ron Johnson wrote: > On Thu, Dec 5, 2024 at 6:24=E2=80=AFPM David G. Johnston < > david.g.johnston@gmail.com> wrote: > >> On Thu, Dec 5, 2024 at 4:04=E2=80=AFPM Ron Johnson >> wrote: >> >>> Another alternative is to open the .sql file in Notepad++, then add >>> "public." before all the unqualified "earth" and "ll_to_earth" referenc= es. >>> >> >> And as discussed there are none in that file because those references ar= e >> within an extension's objects and only create extension appears in the f= ile. >> > > 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 =E2=80=9Cearth=E2=80=9D func= tion name to the one existing in the public schema when looking through the session=E2=80=99= s search_path. David J. --000000000000162f36062892fb89 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, December 5, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thu, Dec 5, 2024 at 6:24= =E2=80=AFPM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, De= c 5, 2024 at 4:04=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
Another alt= ernative 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 on= ly create extension appears in the file.

Then why would changing search_path work?=C2=A0
=


Because (I presume) f= unction inlining during execution of create table keeps the search_path of = the session executing create table which will then result in the parser res= olving the unqualified =E2=80=9Cearth=E2=80=9D function name to the one exi= sting in the public schema when looking through the session=E2=80=99s searc= h_path.

David J.

--000000000000162f36062892fb89--