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 1jaxTs-0000pl-Pa for pgsql-bugs@arkaria.postgresql.org; Tue, 19 May 2020 08:20:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jaxTr-00064d-OP for pgsql-bugs@arkaria.postgresql.org; Tue, 19 May 2020 08:20:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jaxTr-00064V-CJ for pgsql-bugs@lists.postgresql.org; Tue, 19 May 2020 08:20:07 +0000 Received: from mail-pj1-x1042.google.com ([2607:f8b0:4864:20::1042]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jaxTp-0004ly-03 for pgsql-bugs@lists.postgresql.org; Tue, 19 May 2020 08:20:06 +0000 Received: by mail-pj1-x1042.google.com with SMTP id a5so996578pjh.2 for ; Tue, 19 May 2020 01:20:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=Jv4Ye9sXQ7Yx3ckM+KtfZEfO7M/NkDxThNSWHFi47m8=; b=Jmmtx1SgpWFYg0DKdWBGwvre7rfFmmIiehNJ/0S6jbkV6YQpWHBHw9+QchVyiydzGv YfScgIChs6+9o7J7kJuCKfv2NiQXgQ7eyfe/w4LPoNoIxf8z0CQqZVvCWGEMw6yj0yF5 itrTbRgAwXTdcfTE2n5zWJu0Wfx0jV2G3OS8X6VYSiuBeZdJunkvWWCmxDAsAhaEnlXN ei+ZpH8dAa8kAlER1oR8oZN6HyGHCel4kTY6lYG6wSZpWtxf0+CqLNrA/lSTFxp9/uE+ JzHDI8XLZQAV/rZzoOB0L7f1fM1gnOKUlAxBFiv5CdQ0Tfz/nJHMr6+ZYyoE4CSKYf6q mY2Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=Jv4Ye9sXQ7Yx3ckM+KtfZEfO7M/NkDxThNSWHFi47m8=; b=ibaC1gJMcbQxa0XbHnvb358dLuvC48gapkpPw4cx9qPA9TnJ/MYM07/CruZ1zlLgMu 8xObZ8g6U59rtmJZhCfRAdELpPVET0PCvRIy4xCpXbES6ce0BKp0O68JZiArq5ScYMLj HUK+ewg3NLnaGH7tTa9BxoV/03V76nUpuXEUJk4nS6LRbthP9M+dZXvv6fPqhDpq3rsj rXYkO10Jefl12Qx7aNzMWh2uLNa8e8kl6ABiqXl24z3BGbP2fz2Lv/MJIda2VkY+t/Ud Q7KNaor8OlOAqa5Vql70ggUtE4MBD0+ri3/ISFS0NUKVKgGVLjs/PDQJhjEP8JTqK25C rEuw== X-Gm-Message-State: AOAM532IOF30ccRseLFIvwddhmiqcEClccO3k8AOD4aKwqX1b9JFAmWM rlIYDju5dCBiuyHukOvTuUA= X-Google-Smtp-Source: ABdhPJxvKvK0SzhPXrVIuGgQISaFRtgTlBD1tnm1Kc8M2fHwaefCQ8FHOSMVl2WLmQFLGDqZbEhwhQ== X-Received: by 2002:a17:902:d90c:: with SMTP id c12mr19634092plz.113.1589876403907; Tue, 19 May 2020 01:20:03 -0700 (PDT) Received: from [127.0.0.1] ([198.11.176.14]) by smtp.gmail.com with ESMTPSA id i21sm9551748pgn.20.2020.05.19.01.19.59 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 19 May 2020 01:20:03 -0700 (PDT) From: wenjing zeng Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_EB518DF2-F63E-43B4-B1E3-678D139CF02A" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: [bug] Table not have typarray when created by single user mode Date: Tue, 19 May 2020 16:19:56 +0800 In-Reply-To: <20200414180019.GA16161@alvherre.pgsql> Cc: pgsql-bugs@lists.postgresql.org, Tom Lane , wjzeng To: Alvaro Herrera , tushar , Prabhat Sahu References: <20200414180019.GA16161@alvherre.pgsql> X-Mailer: Apple Mail (2.3608.80.23.2.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_EB518DF2-F63E-43B4-B1E3-678D139CF02A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 2020=E5=B9=B44=E6=9C=8815=E6=97=A5 =E4=B8=8A=E5=8D=882:00=EF=BC=8CAlvaro= Herrera =E5=86=99=E9=81=93=EF=BC=9A >=20 > On 2020-Apr-14, wenjing wrote: >=20 >> However, if such a table exists, an error with pg_upgrade is further = raised >>=20 >> ./initdb -k -D datanew >> ./pg_upgrade -d data -d datanew - b. -b. >>=20 >> Restoring database schemas in the new cluster >> postgres =20 >> *failure* >>=20 >> Consult the last few lines of "pg_upgrade_dump_13580.log" for >> the probable cause of the failure. >> Failure, exiting >>=20 >> pg_restore: from TOC entry 200; 1259 13581 TABLE = t_create_by_standalone wenjing >> pg_restore: error: could not execute query: ERROR: pg_type array OID = value not set when in binary upgrade mode >=20 > Maybe the solution is to drop the table before pg_upgrade. >=20 > (Perhaps in --check mode pg_upgrade could warn you about such > situations. But then, should it warn you specifically about random > other instances of catalog corruption?) I fixed the problem along your lines. Please check. Wenjing --Apple-Mail=_EB518DF2-F63E-43B4-B1E3-678D139CF02A Content-Disposition: attachment; filename=check_for_incomplete_object_definition_v1.txt Content-Type: text/plain; x-unix-mode=0644; name="check_for_incomplete_object_definition_v1.txt" Content-Transfer-Encoding: quoted-printable diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c index 00aef85..abb5f8a 100644 --- a/src/bin/pg_upgrade/check.c +++ b/src/bin/pg_upgrade/check.c @@ -27,6 +27,7 @@ static void check_for_reg_data_type_usage(ClusterInfo = *cluster); static void check_for_jsonb_9_4_usage(ClusterInfo *cluster); static void check_for_pg_role_prefix(ClusterInfo *cluster); static char *get_canonical_locale_name(int category, const char = *locale); +static void check_for_incomplete_object_definition(ClusterInfo = *cluster); =20 =20 /* @@ -142,6 +143,9 @@ check_and_dump_old_cluster(bool live_check) if (GET_MAJOR_VERSION(old_cluster.major_version) <=3D 804) new_9_0_populate_pg_largeobject_metadata(&old_cluster, = true); =20 + if (GET_MAJOR_VERSION(old_cluster.major_version) >=3D 802 && = user_opts.check) + check_for_incomplete_object_definition(&old_cluster); + /* * While not a check option, we do this now because this is the = only time * the old server is running. @@ -1246,3 +1250,81 @@ get_canonical_locale_name(int category, const = char *locale) =20 return res; } + +/* + * check_for_incomplete_object_definition() + */ +static void +check_for_incomplete_object_definition(ClusterInfo *cluster) +{ + int dbnum; + FILE *script =3D NULL; + bool found =3D false; + char output_path[MAXPGPATH]; + + prep_status("Checking for incomplete object definition"); + + snprintf(output_path, sizeof(output_path), = "incomplete_object_definition.txt"); + + for (dbnum =3D 0; dbnum < cluster->dbarr.ndbs; dbnum++) + { + PGresult *res; + bool db_used =3D false; + int ntups; + int rowno; + int i_nspname, + i_relname; + DbInfo *active_db =3D &cluster->dbarr.dbs[dbnum]; + PGconn *conn =3D connectToServer(cluster, = active_db->db_name); + + res =3D executeQueryOrDie(conn, + "SELECT = n.nspname, c.relname " + "FROM = pg_catalog.pg_class c, " + " = pg_catalog.pg_namespace n, " + " = pg_catalog.pg_type t " + "WHERE = c.relkind in('r', 'v', 'm', 'p', 'f') AND " + " = t.oid =3D c.reltype AND " + " = t.typarray =3D 0 AND " + " = c.relnamespace =3D n.oid AND " + " = n.nspname NOT IN ('pg_catalog', 'information_schema')"); + + ntups =3D PQntuples(res); + i_nspname =3D PQfnumber(res, "nspname"); + i_relname =3D PQfnumber(res, "relname"); + for (rowno =3D 0; rowno < ntups; rowno++) + { + found =3D true; + if (script =3D=3D NULL && (script =3D = fopen_priv(output_path, "w")) =3D=3D NULL) + pg_fatal("could not open file \"%s\": = %s\n", + output_path, = strerror(errno)); + if (!db_used) + { + fprintf(script, "In database: %s\n", = active_db->db_name); + db_used =3D true; + } + fprintf(script, " %s.%s\n", + PQgetvalue(res, rowno, = i_nspname), + PQgetvalue(res, rowno, = i_relname)); + } + + PQclear(res); + + PQfinish(conn); + } + + if (script) + fclose(script); + + if (found) + { + pg_log(PG_REPORT, "fatal\n"); + pg_fatal("Your installation contains one or more = incomplete table or view definitions.\n" + "They are probably created in = single-user mode.\n" + "so this cluster cannot currently be = upgraded.\n" + "You can remove the problem tables or = views and restart the upgrade.\n" + "A list of the problem objects is in = the file:\n" + " %s\n\n", output_path); + } + else + check_ok(); +} --Apple-Mail=_EB518DF2-F63E-43B4-B1E3-678D139CF02A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 >=20 > --=20 > =C3=81lvaro Herrera https://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --Apple-Mail=_EB518DF2-F63E-43B4-B1E3-678D139CF02A--