pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: wenjing zeng <wjzeng2012@gmail.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
To: tushar <tushar.ahuja@enterprisedb.com>
To: Prabhat Sahu <prabhat.sahu@enterprisedb.com>
Cc: pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Cc: wjzeng <wjzeng2012@gmail.com>
Subject: Re: [bug] Table not have typarray when created by single user mode
Date: Tue, 19 May 2020 16:19:56 +0800
Message-ID: <B9D7EB43-21D6-46C9-B74A-0DD1A6D3C2C7@gmail.com> (raw)
In-Reply-To: <20200414180019.GA16161@alvherre.pgsql>
References: <20200414180019.GA16161@alvherre.pgsql>


> 2020年4月15日 上午2:00,Alvaro Herrera <alvherre@2ndquadrant.com> 写道:
> 
> On 2020-Apr-14, wenjing wrote:
> 
>> However, if such a table exists, an error with pg_upgrade is further raised
>> 
>> ./initdb -k -D datanew
>> ./pg_upgrade -d data -d datanew - b. -b.
>> 
>> Restoring database schemas in the new cluster
>>  postgres                                                  
>> *failure*
>> 
>> Consult the last few lines of "pg_upgrade_dump_13580.log" for
>> the probable cause of the failure.
>> Failure, exiting
>> 
>> 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
> 
> Maybe the solution is to drop the table before pg_upgrade.
> 
> (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

> 
> -- 
> Álvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

check_for_incomplete_object_definition_v1.txt (text/plain, 3.2K) download 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); /* @@ -142,6 +143,9 @@ check_and_dump_old_cluster(bool live_check) if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804) new_9_0_populate_pg_largeobject_metadata(&old_cluster, true); + if (GET_MAJOR_VERSION(old_cluster.major_version) >= 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) return res; } + +/* + * check_for_incomplete_object_definition() + */ +static void +check_for_incomplete_object_definition(ClusterInfo *cluster) +{ + int dbnum; + FILE *script = NULL; + bool found = false; + char output_path[MAXPGPATH]; + + prep_status("Checking for incomplete object definition"); + + snprintf(output_path, sizeof(output_path), "incomplete_object_definition.txt"); + + for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++) + { + PGresult *res; + bool db_used = false; + int ntups; + int rowno; + int i_nspname, + i_relname; + DbInfo *active_db = &cluster->dbarr.dbs[dbnum]; + PGconn *conn = connectToServer(cluster, active_db->db_name); + + res = 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 = c.reltype AND " + " t.typarray = 0 AND " + " c.relnamespace = n.oid AND " + " n.nspname NOT IN ('pg_catalog', 'information_schema')"); + + ntups = PQntuples(res); + i_nspname = PQfnumber(res, "nspname"); + i_relname = PQfnumber(res, "relname"); + for (rowno = 0; rowno < ntups; rowno++) + { + found = true; + if (script == NULL && (script = fopen_priv(output_path, "w")) == 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 = 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(); +}

view thread (14+ messages)  latest in thread

Message-ID: <B9D7EB43-21D6-46C9-B74A-0DD1A6D3C2C7@gmail.com>
Permalink:  ../B9D7EB43-21D6-46C9-B74A-0DD1A6D3C2C7@gmail.com/
Also on:    postgresql.org/message-id/B9D7EB43-21D6-46C9-B74A-0DD1A6D3C2C7@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: wjzeng2012@gmail.com, alvherre@2ndquadrant.com, tushar.ahuja@enterprisedb.com, prabhat.sahu@enterprisedb.com, tgl@sss.pgh.pa.us
  Subject: Re: [bug] Table not have typarray when created by single user mode
  In-Reply-To: <B9D7EB43-21D6-46C9-B74A-0DD1A6D3C2C7@gmail.com>
  References: <20200414180019.GA16161@alvherre.pgsql> <B9D7EB43-21D6-46C9-B74A-0DD1A6D3C2C7@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox