Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEvYF-0002Kn-7U for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jul 2022 16:30:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oEvYE-0005FC-2W for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jul 2022 16:30:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEvYD-0005A6-La for pgsql-admin@lists.postgresql.org; Fri, 22 Jul 2022 16:30:53 +0000 Received: from mail-ej1-x631.google.com ([2a00:1450:4864:20::631]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oEvYB-0002re-7K for pgsql-admin@postgresql.org; Fri, 22 Jul 2022 16:30:53 +0000 Received: by mail-ej1-x631.google.com with SMTP id va17so9517762ejb.0 for ; Fri, 22 Jul 2022 09:30:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=9z0/NGx4QymAUBJaa1/UfSi+AbDSTXOaLwy0S80WVD8=; b=STgnDhmzsv5vwpduhNq9tR0UX6rTP0nrcDjjWEeU9m/J4LHV0W4C1Dcox3rYMOxyjN WMU5BZAJwpIMJNFgE74tKUYTI3pmzuXhRjEl6nZzFvUeBvpdfw/1zSIBxYhojCdA7EUt uk+n0R7uHzDwl4thj4sBivBwVd2beGlIh1Sl/QMKhAqEqP71cKgxWY95RJigzqH9LXnc oZSWmR2eCFCfUplPnPOFib8WsFlKHSQWAZRbTCoiyZnD4NFvcv8METSU4raIdtRza4Qb x4/Aa+Qxy0CueHDISi+/8CRFkJ4luzB1qmtBKUOpqUMayirVHvhXAwHcNDE8pEd2Zp14 Q/ZQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=9z0/NGx4QymAUBJaa1/UfSi+AbDSTXOaLwy0S80WVD8=; b=OVJHpft7xuADU5o+7YhaJeo4UVxlsDZ8tNx3nnZhmpeBsbsvPCjwQeOlUsM9IBq/qS VriNoBGVJ6/qcfhFqsQ4mnL80+o/Nf99bInYCPxMnEBaAfj3OgQvV7p6Bq67P7RPBLHG ljzicH6/EqUFy9nkLMHZE+PO/0rZpHjRdKoWoRznk2JBm8LRl1OzTgDqRbhjdf+3YWJZ V1yZpON6jMrL97ieCh6UJo1TDifb/86fJyqlC1SYy9+7lZOdDzaTsZ6TvAvx2wEQCgzU OQLZialEXRXocDyGc9BwLKCMwYe2CcqDxzBjKb+ffcSVX1d5fp7KlcPvx8jk7J49g+i6 WL3w== X-Gm-Message-State: AJIora+mTRR4WJvv3zlSPQBj63rLows1G/s9OKg2R6Sk9AdfU9iVZl3c Lt+bciOYs/fEDezUmPtszC68Noy1SWHvvJI5Ufk= X-Google-Smtp-Source: AGRyM1vkQz+Nk2AkKsHKoVLIcilzmk7Mn8RUvj1UgeTtqaB/9ZI1MiMSC8lLkiD93kI7RfxN3Iac7iEONvMdlMqqhg4= X-Received: by 2002:a17:907:2e02:b0:72b:7f58:34a7 with SMTP id ig2-20020a1709072e0200b0072b7f5834a7mr524687ejc.525.1658507449801; Fri, 22 Jul 2022 09:30:49 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Fri, 22 Jul 2022 09:30:32 -0700 Message-ID: Subject: Re: pg_upgrade failure upgrading from v10.8 to v14.4 To: Murthy Nunna Cc: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary="00000000000067214905e4675bb4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000067214905e4675bb4 Content-Type: text/plain; charset="UTF-8" On Fri, Jul 22, 2022 at 9:19 AM Murthy Nunna wrote: > > I encountered following error. Any help or insight is much appreciated. > > > CREATE VIEW "public"."all_tables" AS > > SELECT ((("n"."nspname")::"text" || '.'::"text") || > ("c"."relname")::"text") AS "tablename", > > > You created a view that uses the catalogs and the structure of those catalogs has changed. pg_upgrade cannot fix this for you. Unfortunately, the only real solution is to have a pre-upgrade script that removes the not unusable view, perform the upgrade, and then run a post-upgrade script that replaces it with something that will work in the new system. I'm unaware of a version of PostgreSQL that would act as a transition version where both versions of the view could exist, but you may wish to double-check that if you think such a two-step upgrade path would be easier for you to manage. David J. --00000000000067214905e4675bb4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Jul 22, 2022 at 9:19 AM Murthy Nunna <mnunna@fnal.gov> wrote:
=


I encountered followi= ng error. Any help or insight is much appreciated.


CREATE VIEW "pub= lic"."all_tables" AS

SELECT ((("n&quo= t;."nspname")::"text" || '.'::"text")= || ("c"."relname")::"text") AS "tablena= me",



Y= ou created a view that uses the catalogs and the structure of those catalog= s has changed.=C2=A0 pg_upgrade cannot fix this for you.=C2=A0 Unfortunatel= y, the only real solution is to have a pre-upgrade script that removes the = not unusable view, perform the upgrade, and then run a post-upgrade script = that replaces it with something that will work in the new system.=C2=A0 I&#= 39;m unaware of a version of PostgreSQL that would act as a transition vers= ion where both versions of the view could exist, but you may wish to double= -check that if you think such a two-step upgrade path would be easier for y= ou to manage.

David J.
--00000000000067214905e4675bb4--