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 1jOC4A-0002Vt-2t for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Apr 2020 03:16:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jOC48-0008MV-OI for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Apr 2020 03:16:48 +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 1jOC48-0008MO-6h for pgsql-bugs@lists.postgresql.org; Tue, 14 Apr 2020 03:16:48 +0000 Received: from mail-pg1-x542.google.com ([2607:f8b0:4864:20::542]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jOC45-00030c-14 for pgsql-bugs@lists.postgresql.org; Tue, 14 Apr 2020 03:16:47 +0000 Received: by mail-pg1-x542.google.com with SMTP id d17so5417417pgo.0 for ; Mon, 13 Apr 2020 20:16:44 -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=cfgiyBFhYXQ+uLz3UthPYaAwNtAlKqy0SnZtM8rvEJ0=; b=MGjIhGvJUHVfmmYtzPgpR+epDbefE2tBYp4PHScmaKzDFnXyuYHl//D2iDhYF0dicU 4ID6nbud/sDqKwbyAaCL60vFSZBFEKGh87LVJEAOctZ/8k3AvmgIyL/dubcxFNluGC3D OyXMYMyDI+GPKnl0EG5mKAKOpuNfcj0fvWJBN3oHiTt7hctI1h8GjI+U6Wzh6dK6/vQq vsM/3wDEkhllBk2Cc1agCtFXxGK9OQ0RLPMgXbX49UeFgKpacwDJ+N/prFhh2TmYC7f4 oI6kValBXnFKxAWoYmBpN8MBwSNBIytXetpff05HMMhaWlIB3m2ARD/y5T6GufVtmk54 06Ag== 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=cfgiyBFhYXQ+uLz3UthPYaAwNtAlKqy0SnZtM8rvEJ0=; b=NgljInCJoIMIYMbnOE6OU9jtnWXZ8vRQG7pZSZdc2T8fCTo4PJFEuFaaG5BA7rxJk8 9zRdjVZSphG7eCwLMZZbZG/X4OMdvT4uVqcNY6vqAWlvG/kDS6VNCLxVWRWkzlHyMkQl mzcQY66QfGmRiEl/IYomAVJvLVM/3sb8A5AbjufnmFAwBHBDKQGKI9GBxWWtvArgNGSI iV9Bf33PqiqKq1F+ihrwcI1Qs4bMk5KPOvBH5jq27d3tEhUTprTh1kaCCdSXyzo+iAfh hYfcLPn5zIyg5yQyvH4goB5vUNTs0mA7zQlmO9f+WfMKSTZzvmjik0/J+FY/LcLF2WPg aJLw== X-Gm-Message-State: AGi0PuY6HwAh3B0Ry3pQSu4t5s7OK/2gznXvb4QHHRu9CukGbgC2dHQI WLLyD6iRwbQDghb/dVnXJDI= X-Google-Smtp-Source: APiQypIVcazCHoq1vj0Q8rh7si8sLn1C05/84Z7ovo+oxX8OK1ysetKEbbMxM9U0IpZsnc8usgykUw== X-Received: by 2002:aa7:978a:: with SMTP id o10mr21162762pfp.121.1586834203197; Mon, 13 Apr 2020 20:16:43 -0700 (PDT) Received: from [127.0.0.1] ([47.251.3.230]) by smtp.gmail.com with ESMTPSA id f10sm10469834pjt.7.2020.04.13.20.16.40 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 13 Apr 2020 20:16:42 -0700 (PDT) From: wenjing Message-Id: <9FCD07FF-CAF6-4220-8E07-E4E8037D6899@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_E5C0AFB6-C72E-4132-8492-55CB11DB36AD" 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, 14 Apr 2020 11:16:36 +0800 In-Reply-To: <32412.1586789476@sss.pgh.pa.us> Cc: pgsql-bugs@lists.postgresql.org, tushar To: Tom Lane References: <761F1389-C6A8-4C15-80CE-950C961F5341@gmail.com> <32412.1586789476@sss.pgh.pa.us> 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=_E5C0AFB6-C72E-4132-8492-55CB11DB36AD Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 2020=E5=B9=B44=E6=9C=8813=E6=97=A5 =E4=B8=8B=E5=8D=8810:51=EF=BC=8CTom = Lane =E5=86=99=E9=81=93=EF=BC=9A >=20 > wenjing writes: >> Use single user mode (t_create_by_standalone) typarray =3D 0, but use = psql t_create_by_psql typarray has oid. >=20 > That's because of this in heap_create_with_catalog: >=20 > /* > * Decide whether to create an array type over the relation's = rowtype. We > * do not create any array types for system catalogs (ie, those = made > * during initdb). We do not create them where the use of a = relation as > * such is an implementation detail: toast tables, sequences and = indexes. > */ > if (IsUnderPostmaster && (relkind =3D=3D RELKIND_RELATION || > relkind =3D=3D RELKIND_VIEW || > relkind =3D=3D RELKIND_MATVIEW || > relkind =3D=3D RELKIND_FOREIGN_TABLE || > relkind =3D=3D RELKIND_COMPOSITE_TYPE || > relkind =3D=3D = RELKIND_PARTITIONED_TABLE)) > new_array_oid =3D AssignTypeArrayOid(); >=20 > Admittedly, "!IsUnderPostmaster" is not exactly the same thing as = "running > during initdb", but I do not consider this a bug. You generally = should > not be using single-user mode for anything except disaster recovery. >=20 > regards, tom lane Thanks for explain. I can understand your point. 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 =20 *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 Command was: -- For binary upgrade, must preserve pg_type oid SELECT = pg_catalog.binary_upgrade_set_next_pg_type_oid('13582'::pg_catalog.oid); I wonder if there are any restrictions that need to be put in somewhere? Wenjing --Apple-Mail=_E5C0AFB6-C72E-4132-8492-55CB11DB36AD Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

2020=E5=B9=B44=E6=9C=8813=E6=97=A5 =E4=B8=8B=E5=8D=8810:51=EF=BC= =8CTom Lane <tgl@sss.pgh.pa.us> =E5=86=99=E9=81=93=EF=BC=9A

wenjing <wjzeng2012@gmail.com> writes:
Use single user mode (t_create_by_standalone) = typarray =3D 0, but use psql t_create_by_psql typarray has oid.

That's because of this in = heap_create_with_catalog:

=    /*
    * Decide = whether to create an array type over the relation's rowtype. We
    * do not create any array types for = system catalogs (ie, those made
    * = during initdb). We do not create them where the use of a relation as
    * such is an implementation detail: = toast tables, sequences and indexes.
=     */
   if = (IsUnderPostmaster && (relkind =3D=3D RELKIND_RELATION ||
=             &n= bsp;           &nbs= p;    relkind =3D=3D RELKIND_VIEW ||
=             &n= bsp;           &nbs= p;    relkind =3D=3D RELKIND_MATVIEW ||
= =             &n= bsp;           &nbs= p;    relkind =3D=3D RELKIND_FOREIGN_TABLE ||
=             &n= bsp;           &nbs= p;    relkind =3D=3D RELKIND_COMPOSITE_TYPE ||
=             &n= bsp;           &nbs= p;    relkind =3D=3D RELKIND_PARTITIONED_TABLE))
       new_array_oid =3D = AssignTypeArrayOid();

Admittedly, = "!IsUnderPostmaster" is not exactly the same thing as "running
during initdb", but I do not consider this a bug.  You = generally should
not be using single-user mode for = anything except disaster recovery.

regards, = tom lane

Thanks for explain. I can = understand your point.

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
Command was:
-- For binary upgrade, must preserve pg_type = oid
SELECT = pg_catalog.binary_upgrade_set_next_pg_type_oid('13582'::pg_catalog.oid);

I wonder if there are any restrictions that = need to be put in somewhere?



Wenjing




= --Apple-Mail=_E5C0AFB6-C72E-4132-8492-55CB11DB36AD--