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 1rYNGS-00Feu4-UZ for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Feb 2024 09:33:45 +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 1rYNGR-007aG7-E1 for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Feb 2024 09:33:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYNGQ-007aFy-Vv for pgsql-hackers@lists.postgresql.org; Fri, 09 Feb 2024 09:33:43 +0000 Received: from smtp.outgoing.loopia.se ([93.188.3.37]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYNGO-006HIm-52 for pgsql-hackers@lists.postgresql.org; Fri, 09 Feb 2024 09:33:42 +0000 Received: from s807.loopia.se (localhost [127.0.0.1]) by s807.loopia.se (Postfix) with ESMTP id 630EA2FF124D for ; Fri, 9 Feb 2024 10:33:38 +0100 (CET) Received: from s979.loopia.se (unknown [172.22.191.5]) by s807.loopia.se (Postfix) with ESMTP id 51A3F2E28200; Fri, 9 Feb 2024 10:33:38 +0100 (CET) Received: from s472.loopia.se (unknown [172.22.191.6]) by s979.loopia.se (Postfix) with ESMTP id 4D86610BC487; Fri, 9 Feb 2024 10:33:38 +0100 (CET) X-Virus-Scanned: amavisd-new at amavis.loopia.se X-Spam-Flag: NO X-Spam-Score: -1.01 X-Spam-Level: X-Spam-Status: No, score=-1.01 tagged_above=-999 required=6.2 tests=[ALL_TRUSTED=-1, T_SCC_BODY_TEXT_LINE=-0.01] autolearn=disabled Received: from s899.loopia.se ([172.22.191.5]) by s472.loopia.se (s472.loopia.se [172.22.190.12]) (amavisd-new, port 10024) with UTF8LMTP id 7yj18YEkDQa0; Fri, 9 Feb 2024 10:33:37 +0100 (CET) X-Loopia-Auth: user X-Loopia-User: daniel@yesql.se X-Loopia-Originating-IP: 89.255.232.193 Received: from smtpclient.apple (customer-89-255-232-193.stosn.net [89.255.232.193]) (Authenticated sender: daniel@yesql.se) by s899.loopia.se (Postfix) with ESMTPSA id 9D7532C8BAB1; Fri, 9 Feb 2024 10:33:37 +0100 (CET) From: Daniel Gustafsson Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_5F4F1FED-341B-4064-804D-6FC9A45CCB46" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.4\)) Subject: Re: Reducing connection overhead in pg_upgrade compat check phase Date: Fri, 9 Feb 2024 10:33:37 +0100 In-Reply-To: Cc: Nathan Bossart , vignesh C , PostgreSQL Hackers To: Peter Eisentraut References: <20230710230907.GA481155@nathanxps13> <1FE28680-333E-433C-9809-95662037B0AA@yesql.se> <22CDC82E-8959-4D92-B1B0-069558011F1C@yesql.se> <20230711233606.GA776188@nathanxps13> <19245526-3F25-4F84-BB80-21DA034F8650@yesql.se> <7B104870-5A35-4FC1-AF4F-7E9C8EDBDF18@yesql.se> <20240206163213.GA3841008@nathanxps13> <824892FE-63DD-4771-9D4E-B14A955C3F95@yesql.se> <0AAE64D6-00ED-472E-935D-C8124AC37CFC@yesql.se> <1523cf0f-98a3-4b08-bec4-f68162cac9bb@eisentraut.org> X-Mailer: Apple Mail (2.3696.120.41.1.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_5F4F1FED-341B-4064-804D-6FC9A45CCB46 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On 9 Feb 2024, at 00:04, Daniel Gustafsson wrote: >=20 >> On 8 Feb 2024, at 15:16, Daniel Gustafsson wrote: >=20 >> One option could perhaps be to include a version number for <=3D = comparison, and >> if set to zero a function pointer to a version check function must be = provided? >> That would handle the simple cases in a single place without messy = logic, and >> leave the more convoluted checks with a special case function. >=20 > The attached is a draft version of this approach, each check can = define to run > for all versions, set a threshold version for which it runs or define = a > callback which implements a more complicated check. And again pgindented and with documentation on the struct members to = make it easy to add new checks. A repetitive part of the report text was also = moved to a single place. -- Daniel Gustafsson --Apple-Mail=_5F4F1FED-341B-4064-804D-6FC9A45CCB46 Content-Disposition: attachment; filename=v14-0001-pg_upgrade-run-all-data-type-checks-per-connecti.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v14-0001-pg_upgrade-run-all-data-type-checks-per-connecti.patch" Content-Transfer-Encoding: quoted-printable =46rom=202d9ec92dd3af34ab240364445a404fef2d46caa1=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Daniel=20Gustafsson=20= =0ADate:=20Wed,=207=20Feb=202024=2013:36:46=20= +0100=0ASubject:=20[PATCH=20v14]=20pg_upgrade:=20run=20all=20data=20type=20= checks=20per=20connection=0A=0AThe=20checks=20for=20data=20type=20usage=20= were=20each=20connecting=20to=20all=20databases=0Ain=20the=20cluster=20= and=20running=20their=20query.=20On=20cluster=20which=20have=20a=20lot=0A= of=20databases=20this=20can=20become=20unnecessarily=20expensive.=20This=20= moves=20the=0Achecks=20to=20run=20in=20a=20single=20connection=20instead=20= to=20minimize=20connection=0Asetup/teardown=20overhead.=0A=0A= Reviewed-by:=20Nathan=20Bossart=20=0A= Reviewed-by:=20Justin=20Pryzby=20=0AReviewed-by:=20= Peter=20Eisentraut=20=0ADiscussion:=20= https://postgr.es/m/BB4C76F-D416-4F9F-949E-DBE950D37787@yesql.se=0A---=0A= =20src/bin/pg_upgrade/check.c=20=20=20=20=20=20|=20750=20= +++++++++++++++++++++-----------=0A=20src/bin/pg_upgrade/pg_upgrade.h=20= |=20=2013=20+-=0A=20src/bin/pg_upgrade/version.c=20=20=20=20|=20266=20= +----------=0A=203=20files=20changed,=20507=20insertions(+),=20522=20= deletions(-)=0A=0Adiff=20--git=20a/src/bin/pg_upgrade/check.c=20= b/src/bin/pg_upgrade/check.c=0Aindex=20e36a7328bf..6d640d7bb0=20100644=0A= ---=20a/src/bin/pg_upgrade/check.c=0A+++=20b/src/bin/pg_upgrade/check.c=0A= @@=20-10,6=20+10,7=20@@=0A=20#include=20"postgres_fe.h"=0A=20=0A=20= #include=20"catalog/pg_authid_d.h"=0A+#include=20"catalog/pg_class_d.h"=0A= =20#include=20"catalog/pg_collation.h"=0A=20#include=20= "fe_utils/string_utils.h"=0A=20#include=20"mb/pg_wchar.h"=0A@@=20-23,13=20= +24,6=20@@=20static=20void=20= check_for_isn_and_int8_passing_mismatch(ClusterInfo=20*cluster);=0A=20= static=20void=20check_for_user_defined_postfix_ops(ClusterInfo=20= *cluster);=0A=20static=20void=20= check_for_incompatible_polymorphics(ClusterInfo=20*cluster);=0A=20static=20= void=20check_for_tables_with_oids(ClusterInfo=20*cluster);=0A-static=20= void=20check_for_composite_data_type_usage(ClusterInfo=20*cluster);=0A= -static=20void=20check_for_reg_data_type_usage(ClusterInfo=20*cluster);=0A= -static=20void=20check_for_aclitem_data_type_usage(ClusterInfo=20= *cluster);=0A-static=20void=20= check_for_removed_data_type_usage(ClusterInfo=20*cluster,=0A-=09=09=09=09= =09=09=09=09=09=09=09=20=20const=20char=20*version,=0A-=09=09=09=09=09=09= =09=09=09=09=09=20=20const=20char=20*datatype);=0A-static=20void=20= check_for_jsonb_9_4_usage(ClusterInfo=20*cluster);=0A=20static=20void=20= check_for_pg_role_prefix(ClusterInfo=20*cluster);=0A=20static=20void=20= check_for_new_tablespace_dir(void);=0A=20static=20void=20= check_for_user_defined_encoding_conversions(ClusterInfo=20*cluster);=0A= @@=20-38,6=20+32,498=20@@=20static=20void=20= check_new_cluster_subscription_configuration(void);=0A=20static=20void=20= check_old_cluster_for_valid_slots(bool=20live_check);=0A=20static=20void=20= check_old_cluster_subscription_state(void);=0A=20=0A+/*=0A+=20*=20= DataTypesUsageChecks=20-=20definitions=20of=20data=20type=20checks=20for=20= the=20old=20cluster=0A+=20*=20in=20order=20to=20determine=20if=20an=20= upgrade=20can=20be=20performed.=20=20See=20the=20comment=20on=0A+=20*=20= data_types_usage_checks=20below=20for=20a=20more=20detailed=20= description.=0A+=20*/=0A+typedef=20struct=0A+{=0A+=09/*=20Status=20line=20= to=20print=20to=20the=20user=20*/=0A+=09const=20char=20*status;=0A+=09/*=20= Filename=20to=20store=20report=20to=20*/=0A+=09const=20char=20= *report_filename;=0A+=09/*=20Query=20to=20extract=20the=20oid=20of=20the=20= datatype=20*/=0A+=09const=20char=20*base_query;=0A+=09/*=20Text=20to=20= store=20to=20report=20in=20case=20of=20error=20*/=0A+=09const=20char=20= *report_text;=0A+=09/*=20The=20latest=20version=20where=20the=20check=20= applies=20*/=0A+=09int=09=09=09threshold_version;=0A+=09/*=20A=20= function=20pointer=20for=20determining=20if=20the=20check=20applies=20*/=0A= +=09DataTypesUsageVersionCheck=20version_hook;=0A+}=09=09=09= DataTypesUsageChecks;=0A+=0A+/*=0A+=20*=20Special=20values=20for=20= threshold_version=20for=20indicating=20that=20a=20check=20applies=20to=0A= +=20*=20all=20versions,=20or=20that=20a=20custom=20function=20needs=20to=20= be=20invoked=20to=20determine=0A+=20*=20if=20the=20check=20applies.=0A+=20= */=0A+#define=20MANUAL_CHECK=201=0A+#define=20ALL_VERSIONS=20-1=0A+=0A= +/*--=0A+=20*=20Data=20type=20usage=20checks.=20Each=20check=20for=20= problematic=20data=20type=20usage=20is=0A+=20*=20defined=20in=20this=20= array=20with=20metadata,=20SQL=20query=20for=20finding=20the=20data=20= type=0A+=20*=20and=20functionality=20for=20deciding=20if=20the=20check=20= is=20applicable=20to=20the=20version=0A+=20*=20of=20the=20old=20cluster.=20= The=20struct=20members=20are=20described=20in=20detail=20below:=0A+=20*=0A= +=20*=20status=09=09=09=09A=20oneline=20string=20which=20can=20be=20= printed=20to=20the=20user=20to=0A+=20*=09=09=09=09=09=09inform=20about=20= progress.=20Should=20not=20end=20with=20newline.=0A+=20*=20= report_filename=09=09The=20filename=20in=20which=20the=20list=20of=20= problems=20detected=20by=0A+=20*=09=09=09=09=09=09the=20check=20will=20= be=20printed.=0A+=20*=20base_query=09=09=09A=20query=20which=20extracts=20= the=20Oid=20of=20the=20datatype=20checked=0A+=20*=09=09=09=09=09=09for.=0A= +=20*=20report_text=09=09=09The=20text=20which=20will=20be=20printed=20= to=20the=20user=20to=20explain=0A+=20*=09=09=09=09=09=09what=20the=20= check=20did,=20and=20why=20it=20failed.=20The=20text=20should=0A+=20*=09=09= =09=09=09=09end=20with=20a=20newline,=20and=20does=20not=20need=20to=20= refer=20to=20the=0A+=20*=09=09=09=09=09=09report_filename=20as=20that=20= is=20automatically=20appended=20to=0A+=20*=09=09=09=09=09=09the=20report=20= with=20the=20path=20to=20the=20log=20folder.=0A+=20*=20threshold_version=09= The=20major=20version=20of=20PostgreSQL=20for=20which=20to=20run=20the=0A= +=20*=09=09=09=09=09=09check.=20Iff=20the=20old=20cluster=20is=20less=20= than,=20or=20equal=20to,=0A+=20*=09=09=09=09=09=09the=20threshold=20= version=20then=20the=20check=20will=20be=20executed.=0A+=20*=09=09=09=09=09= =09If=20the=20old=20version=20is=20greater=20than=20the=20threshold=20= then=0A+=20*=09=09=09=09=09=09the=20check=20is=20skipped.=20If=20the=20= threshold_version=20is=20set=0A+=20*=09=09=09=09=09=09to=20ALL_VERSIONS=20= then=20it=20will=20be=20run=20unconditionally,=0A+=20*=09=09=09=09=09=09= if=20set=20to=20MANUAL_CHECK=20then=20the=20version_hook=20function=0A+=20= *=09=09=09=09=09=09will=20be=20executed=20in=20order=20to=20determine=20= whether=20or=20not=0A+=20*=09=09=09=09=09=09to=20run.=0A+=20*=20= version_hook=09=09=09A=20function=20pointer=20to=20a=20version=20check=20= function=20of=20type=0A+=20*=09=09=09=09=09=09DataTypesUsageVersionCheck=20= which=20is=20used=20to=20determine=0A+=20*=09=09=09=09=09=09if=20the=20= check=20is=20applicable=20to=20the=20old=20cluster.=20If=20the=0A+=20*=09= =09=09=09=09=09version_hook=20returns=20true=20then=20the=20check=20will=20= be=20run,=0A+=20*=09=09=09=09=09=09else=20it=20will=20be=20skipped.=20= The=20function=20will=20only=20be=0A+=20*=09=09=09=09=09=09executed=20= iff=20threshold_version=20is=20set=20to=20MANUAL_CHECK.=0A+=20*/=0A= +static=20DataTypesUsageChecks=20data_types_usage_checks[]=20=3D=0A+{=0A= +=09/*=0A+=09=20*=20Look=20for=20composite=20types=20that=20were=20made=20= during=20initdb=20*or*=20belong=20to=0A+=09=20*=20information_schema;=20= that's=20important=20in=20case=20information_schema=20was=0A+=09=20*=20= dropped=20and=20reloaded.=0A+=09=20*=0A+=09=20*=20The=20cutoff=20OID=20= here=20should=20match=20the=20source=20cluster's=20value=20of=0A+=09=20*=20= FirstNormalObjectId.=20=20We=20hardcode=20it=20rather=20than=20using=20= that=20C=20#define=0A+=09=20*=20because,=20if=20that=20#define=20is=20= ever=20changed,=20our=20own=20version's=20value=20is=0A+=09=20*=20NOT=20= what=20to=20use.=20=20Eventually=20we=20may=20need=20a=20test=20on=20the=20= source=20cluster's=0A+=09=20*=20version=20to=20select=20the=20correct=20= value.=0A+=09=20*/=0A+=09{=0A+=09=09.status=20=3D=20= gettext_noop("Checking=20for=20system-defined=20composite=20types=20in=20= user=20tables"),=0A+=09=09=09.report_filename=20=3D=20= "tables_using_composite.txt",=0A+=09=09=09.base_query=20=3D=0A+=09=09=09= "SELECT=20t.oid=20FROM=20pg_catalog.pg_type=20t=20"=0A+=09=09=09"LEFT=20= JOIN=20pg_catalog.pg_namespace=20n=20ON=20t.typnamespace=20=3D=20n.oid=20= "=0A+=09=09=09"=20WHERE=20typtype=20=3D=20'c'=20AND=20(t.oid=20<=2016384=20= OR=20nspname=20=3D=20'information_schema')",=0A+=09=09=09.report_text=20= =3D=0A+=09=09=09gettext_noop("Your=20installation=20contains=20= system-defined=20composite=20types=20in=20user=20tables.\n"=0A+=09=09=09=09= =09=09=20"These=20type=20OIDs=20are=20not=20stable=20across=20PostgreSQL=20= versions,\n"=0A+=09=09=09=09=09=09=20"so=20this=20cluster=20cannot=20= currently=20be=20upgraded.=20=20You=20can=20drop=20the\n"=0A+=09=09=09=09= =09=09=20"problem=20columns=20and=20restart=20the=20upgrade.\n"),=0A+=09=09= =09.threshold_version=20=3D=20ALL_VERSIONS=0A+=09},=0A+=0A+=09/*=0A+=09=20= *=209.3=20->=209.4=20Fully=20implement=20the=20'line'=20data=20type=20in=20= 9.4,=20which=0A+=09=20*=20previously=20returned=20"not=20enabled"=20by=20= default=20and=20was=20only=20functionally=0A+=09=20*=20enabled=20with=20= a=20compile-time=20switch;=20as=20of=209.4=20"line"=20has=20a=20= different=0A+=09=20*=20on-disk=20representation=20format.=0A+=09=20*/=0A= +=09{=0A+=09=09.status=20=3D=20gettext_noop("Checking=20for=20= incompatible=20\"line\"=20data=20type"),=0A+=09=09=09.report_filename=20= =3D=20"tables_using_line.txt",=0A+=09=09=09.base_query=20=3D=0A+=09=09=09= "SELECT=20'pg_catalog.line'::pg_catalog.regtype=20AS=20oid",=0A+=09=09=09= .report_text=20=3D=0A+=09=09=09gettext_noop("Your=20installation=20= contains=20the=20\"line\"=20data=20type=20in=20user=20tables.\n"=0A+=09=09= =09=09=09=09=20"this=20data=20type=20changed=20its=20internal=20and=20= input/output=20format\n"=0A+=09=09=09=09=09=09=20"between=20your=20old=20= and=20new=20versions=20so=20this\n"=0A+=09=09=09=09=09=09=20"cluster=20= cannot=20currently=20be=20upgraded.=20=20You=20can\n"=0A+=09=09=09=09=09=09= =20"drop=20the=20problem=20columns=20and=20restart=20the=20upgrade.\n"),=0A= +=09=09=09.threshold_version=20=3D=20903=0A+=09},=0A+=0A+=09/*=0A+=09=20= *=20pg_upgrade=20only=20preserves=20these=20system=20values:=20= pg_class.oid=20pg_type.oid=0A+=09=20*=20pg_enum.oid=0A+=09=20*=0A+=09=20= *=20Many=20of=20the=20reg*=20data=20types=20reference=20system=20catalog=20= info=20that=20is=20not=0A+=09=20*=20preserved,=20and=20hence=20these=20= data=20types=20cannot=20be=20used=20in=20user=20tables=0A+=09=20*=20= upgraded=20by=20pg_upgrade.=0A+=09=20*/=0A+=09{=0A+=09=09.status=20=3D=20= gettext_noop("Checking=20for=20reg*=20data=20types=20in=20user=20= tables"),=0A+=09=09=09.report_filename=20=3D=20"tables_using_reg.txt",=0A= +=0A+=09=09/*=0A+=09=09=20*=20Note:=20older=20servers=20will=20not=20= have=20all=20of=20these=20reg*=20types,=20so=20we=0A+=09=09=20*=20have=20= to=20write=20the=20query=20like=20this=20rather=20than=20depending=20on=20= casts=20to=0A+=09=09=20*=20regtype.=0A+=09=09=20*/=0A+=09=09=09= .base_query=20=3D=0A+=09=09=09"SELECT=20oid=20FROM=20pg_catalog.pg_type=20= t=20"=0A+=09=09=09"WHERE=20t.typnamespace=20=3D=20"=0A+=09=09=09"=20=20=20= =20=20=20=20=20(SELECT=20oid=20FROM=20pg_catalog.pg_namespace=20"=0A+=09=09= =09"=20=20=20=20=20=20=20=20=20WHERE=20nspname=20=3D=20'pg_catalog')=20"=0A= +=09=09=09"=20=20AND=20t.typname=20IN=20(=20"=0A+=09=09/*=20pg_class.oid=20= is=20preserved,=20so=20'regclass'=20is=20OK=20*/=0A+=09=09=09"=20=20=20=20= =20=20=20=20=20=20=20'regcollation',=20"=0A+=09=09=09"=20=20=20=20=20=20=20= =20=20=20=20'regconfig',=20"=0A+=09=09=09"=20=20=20=20=20=20=20=20=20=20=20= 'regdictionary',=20"=0A+=09=09=09"=20=20=20=20=20=20=20=20=20=20=20= 'regnamespace',=20"=0A+=09=09=09"=20=20=20=20=20=20=20=20=20=20=20= 'regoper',=20"=0A+=09=09=09"=20=20=20=20=20=20=20=20=20=20=20= 'regoperator',=20"=0A+=09=09=09"=20=20=20=20=20=20=20=20=20=20=20= 'regproc',=20"=0A+=09=09=09"=20=20=20=20=20=20=20=20=20=20=20= 'regprocedure'=20"=0A+=09=09/*=20pg_authid.oid=20is=20preserved,=20so=20= 'regrole'=20is=20OK=20*/=0A+=09=09/*=20pg_type.oid=20is=20(mostly)=20= preserved,=20so=20'regtype'=20is=20OK=20*/=0A+=09=09=09"=20=20=20=20=20=20= =20=20=20)",=0A+=09=09=09.report_text=20=3D=0A+=09=09=09= gettext_noop("Your=20installation=20contains=20one=20of=20the=20reg*=20= data=20types=20in=20user=20tables.\n"=0A+=09=09=09=09=09=09=20"These=20= data=20types=20reference=20system=20OIDs=20that=20are=20not=20preserved=20= by\n"=0A+=09=09=09=09=09=09=20"pg_upgrade,=20so=20this=20cluster=20= cannot=20currently=20be=20upgraded.=20=20You=20can\n"=0A+=09=09=09=09=09=09= =20"drop=20the=20problem=20columns=20and=20restart=20the=20upgrade.\n"),=0A= +=09=09=09.threshold_version=20=3D=20ALL_VERSIONS=0A+=09},=0A+=0A+=09/*=0A= +=09=20*=20PG=2016=20increased=20the=20size=20of=20the=20'aclitem'=20= type,=20which=20breaks=20the=0A+=09=20*=20on-disk=20format=20for=20= existing=20data.=0A+=09=20*/=0A+=09{=0A+=09=09.status=20=3D=20= gettext_noop("Checking=20for=20incompatible=20\"aclitem\"=20data=20= type"),=0A+=09=09=09.report_filename=20=3D=20"tables_using_aclitem.txt",=0A= +=09=09=09.base_query=20=3D=0A+=09=09=09"SELECT=20= 'pg_catalog.aclitem'::pg_catalog.regtype=20AS=20oid",=0A+=09=09=09= .report_text=20=3D=0A+=09=09=09gettext_noop("Your=20installation=20= contains=20the=20\"aclitem\"=20data=20type=20in=20user=20tables.\n"=0A+=09= =09=09=09=09=09=20"The=20internal=20format=20of=20\"aclitem\"=20changed=20= in=20PostgreSQL=20version=2016\n"=0A+=09=09=09=09=09=09=20"so=20this=20= cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can=20drop=20= the\n"=0A+=09=09=09=09=09=09=20"problem=20columns=20and=20restart=20the=20= upgrade.\n"),=0A+=09=09=09.threshold_version=20=3D=201500=0A+=09},=0A+=0A= +=09/*=0A+=09=20*=20It's=20no=20longer=20allowed=20to=20create=20tables=20= or=20views=20with=20"unknown"-type=0A+=09=20*=20columns.=20=20We=20do=20= not=20complain=20about=20views=20with=20such=20columns,=20because=0A+=09=20= *=20they=20should=20get=20silently=20converted=20to=20"text"=20columns=20= during=20the=20DDL=0A+=09=20*=20dump=20and=20reload;=20it=20seems=20= unlikely=20to=20be=20worth=20making=20users=20do=20that=20by=0A+=09=20*=20= hand.=20=20However,=20if=20there's=20a=20table=20with=20such=20a=20= column,=20the=20DDL=20reload=0A+=09=20*=20will=20fail,=20so=20we=20= should=20pre-detect=20that=20rather=20than=20failing=0A+=09=20*=20= mid-upgrade.=20=20Worse,=20if=20there's=20a=20matview=20with=20such=20a=20= column,=20the=20DDL=0A+=09=20*=20reload=20will=20silently=20change=20it=20= to=20"text"=20which=20won't=20match=20the=20on-disk=0A+=09=20*=20storage=20= (which=20is=20like=20"cstring").=20=20So=20we=20*must*=20reject=20that.=0A= +=09=20*/=0A+=09{=0A+=09=09.status=20=3D=20gettext_noop("Checking=20for=20= invalid=20\"unknown\"=20user=20columns"),=0A+=09=09=09.report_filename=20= =3D=20"tables_using_unknown.txt",=0A+=09=09=09.base_query=20=3D=0A+=09=09= =09"SELECT=20'pg_catalog.unknown'::pg_catalog.regtype=20AS=20oid",=0A+=09= =09=09.report_text=20=3D=0A+=09=09=09gettext_noop("Your=20installation=20= contains=20the=20\"unknown\"=20data=20type=20in=20user=20tables.\n"=0A+=09= =09=09=09=09=09=20"This=20data=20type=20is=20no=20longer=20allowed=20in=20= tables,=20so=20this=20cluster\n"=0A+=09=09=09=09=09=09=20"cannot=20= currently=20be=20upgraded.=20=20You=20can=20drop=20the=20problem=20= columns\n"=0A+=09=09=09=09=09=09=20"and=20restart=20the=20upgrade.\n"),=0A= +=09=09=09.threshold_version=20=3D=20906=0A+=09},=0A+=0A+=09/*=0A+=09=20= *=20PG=2012=20changed=20the=20'sql_identifier'=20type=20storage=20to=20= be=20based=20on=20name,=0A+=09=20*=20not=20varchar,=20which=20breaks=20= on-disk=20format=20for=20existing=20data.=20So=20we=20need=0A+=09=20*=20= to=20prevent=20upgrade=20when=20used=20in=20user=20objects=20(tables,=20= indexes,=20...).=20In=0A+=09=20*=2012,=20the=20sql_identifier=20data=20= type=20was=20switched=20from=20name=20to=20varchar,=0A+=09=20*=20which=20= does=20affect=20the=20storage=20(name=20is=20by-ref,=20but=20not=20= varlena).=20This=0A+=09=20*=20means=20user=20tables=20using=20= sql_identifier=20for=20columns=20are=20broken=20because=0A+=09=20*=20the=20= on-disk=20format=20is=20different.=0A+=09=20*/=0A+=09{=0A+=09=09.status=20= =3D=20gettext_noop("Checking=20for=20invalid=20\"sql_identifier\"=20user=20= columns"),=0A+=09=09=09.report_filename=20=3D=20= "tables_using_sql_identifier.txt",=0A+=09=09=09.base_query=20=3D=0A+=09=09= =09"SELECT=20'information_schema.sql_identifier'::pg_catalog.regtype=20= AS=20oid",=0A+=09=09=09.report_text=20=3D=0A+=09=09=09gettext_noop("Your=20= installation=20contains=20the=20\"sql_identifier\"=20data=20type=20in=20= user=20tables.\n"=0A+=09=09=09=09=09=09=20"The=20on-disk=20format=20for=20= this=20data=20type=20has=20changed,=20so=20this\n"=0A+=09=09=09=09=09=09=20= "cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can=20drop=20= the=20problem\n"=0A+=09=09=09=09=09=09=20"columns=20and=20restart=20the=20= upgrade.\n"),=0A+=09=09=09.threshold_version=20=3D=201100=0A+=09},=0A+=0A= +=09/*=0A+=09=20*=20JSONB=20changed=20its=20storage=20format=20during=20= 9.4=20beta,=20so=20check=20for=20it.=0A+=09=20*/=0A+=09{=0A+=09=09= .status=20=3D=20gettext_noop("Checking=20for=20incompatible=20\"jsonb\"=20= data=20type=20in=20user=20tables"),=0A+=09=09=09.report_filename=20=3D=20= "tables_using_jsonb.txt",=0A+=09=09=09.base_query=20=3D=0A+=09=09=09= "SELECT=20'pg_catalog.jsonb'::pg_catalog.regtype=20AS=20oid",=0A+=09=09=09= .report_text=20=3D=0A+=09=09=09gettext_noop("Your=20installation=20= contains=20the=20\"jsonb\"=20data=20type=20in=20user=20tables.\n"=0A+=09=09= =09=09=09=09=20"The=20internal=20format=20of=20\"jsonb\"=20changed=20= during=209.4=20beta=20so=20this\n"=0A+=09=09=09=09=09=09=20"cluster=20= cannot=20currently=20be=20upgraded.=20=20You=20can=20drop=20the=20= problem=20\n"=0A+=09=09=09=09=09=09=20"columns=20and=20restart=20the=20= upgrade.\n"),=0A+=09=09=09.threshold_version=20=3D=20MANUAL_CHECK,=0A+=09= =09=09.version_hook=20=3D=20jsonb_9_4_check_applicable=0A+=09},=0A+=0A+=09= /*=0A+=09=20*=20PG=2012=20removed=20types=20abstime,=20reltime,=20= tinterval.=0A+=09=20*/=0A+=09{=0A+=09=09.status=20=3D=20= gettext_noop("Checking=20for=20removed=20\"abstime\"=20data=20type=20in=20= user=20tables"),=0A+=09=09=09.report_filename=20=3D=20= "tables_using_abstime.txt",=0A+=09=09=09.base_query=20=3D=0A+=09=09=09= "SELECT=20'pg_catalog.abstime'::pg_catalog.regtype=20AS=20oid",=0A+=09=09= =09.report_text=20=3D=0A+=09=09=09gettext_noop("Your=20installation=20= contains=20the=20\"abstime\"=20data=20type=20in=20user=20tables.\n"=0A+=09= =09=09=09=09=09=20"The=20\"abstime\"=20type=20has=20been=20removed=20in=20= PostgreSQL=20version=2012,\n"=0A+=09=09=09=09=09=09=20"so=20this=20= cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can=20drop=20= the\n"=0A+=09=09=09=09=09=09=20"problem=20columns,=20or=20change=20them=20= to=20another=20data=20type,=20and=20restart\n"=0A+=09=09=09=09=09=09=20= "the=20upgrade.\n"),=0A+=09=09=09.threshold_version=20=3D=201100=0A+=09= },=0A+=09{=0A+=09=09.status=20=3D=20gettext_noop("Checking=20for=20= removed=20\"reltime\"=20data=20type=20in=20user=20tables"),=0A+=09=09=09= .report_filename=20=3D=20"tables_using_reltime.txt",=0A+=09=09=09= .base_query=20=3D=0A+=09=09=09"SELECT=20= 'pg_catalog.reltime'::pg_catalog.regtype=20AS=20oid",=0A+=09=09=09= .report_text=20=3D=0A+=09=09=09gettext_noop("Your=20installation=20= contains=20the=20\"reltime\"=20data=20type=20in=20user=20tables.\n"=0A+=09= =09=09=09=09=09=20"The=20\"reltime\"=20type=20has=20been=20removed=20in=20= PostgreSQL=20version=2012,\n"=0A+=09=09=09=09=09=09=20"so=20this=20= cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can=20drop=20= the\n"=0A+=09=09=09=09=09=09=20"problem=20columns,=20or=20change=20them=20= to=20another=20data=20type,=20and=20restart\n"=0A+=09=09=09=09=09=09=20= "the=20upgrade.\n"),=0A+=09=09=09.threshold_version=20=3D=201100=0A+=09= },=0A+=09{=0A+=09=09.status=20=3D=20gettext_noop("Checking=20for=20= removed=20\"tinterval\"=20data=20type=20in=20user=20tables"),=0A+=09=09=09= .report_filename=20=3D=20"tables_using_tinterval.txt",=0A+=09=09=09= .base_query=20=3D=0A+=09=09=09"SELECT=20= 'pg_catalog.tinterval'::pg_catalog.regtype=20AS=20oid",=0A+=09=09=09= .report_text=20=3D=0A+=09=09=09gettext_noop("Your=20installation=20= contains=20the=20\"tinterval\"=20data=20type=20in=20user=20tables.\n"=0A= +=09=09=09=09=09=09=20"The=20\"tinterval\"=20type=20has=20been=20removed=20= in=20PostgreSQL=20version=2012,\n"=0A+=09=09=09=09=09=09=20"so=20this=20= cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can=20drop=20= the\n"=0A+=09=09=09=09=09=09=20"problem=20columns,=20or=20change=20them=20= to=20another=20data=20type,=20and=20restart\n"=0A+=09=09=09=09=09=09=20= "the=20upgrade.\n"),=0A+=09=09=09.threshold_version=20=3D=201100=0A+=09= },=0A+=0A+=09/*=20End=20of=20checks=20marker,=20must=20remain=20last=20= */=0A+=09{=0A+=09=09NULL,=20NULL,=20NULL,=20NULL,=200,=20NULL=0A+=09}=0A= +};=0A+=0A+/*=0A+=20*=20check_for_data_types_usage()=0A+=20*=09Detect=20= whether=20there=20are=20any=20stored=20columns=20depending=20on=20given=20= type(s)=0A+=20*=0A+=20*=20If=20so,=20write=20a=20report=20to=20the=20= given=20file=20name=20and=20signal=20a=20failure=20to=20the=0A+=20*=20= user.=0A+=20*=0A+=20*=20The=20checks=20to=20run=20are=20defined=20in=20a=20= DataTypesUsageChecks=20structure=20where=0A+=20*=20each=20check=20has=20= a=20metadata=20for=20explaining=20errors=20to=20the=20user,=20a=20= base_query,=0A+=20*=20a=20report=20filename=20and=20a=20function=20= pointer=20hook=20for=20validating=20if=20the=20check=0A+=20*=20should=20= be=20executed=20given=20the=20cluster=20at=20hand.=0A+=20*=0A+=20*=20= base_query=20should=20be=20a=20SELECT=20yielding=20a=20single=20column=20= named=20"oid",=0A+=20*=20containing=20the=20pg_type=20OIDs=20of=20one=20= or=20more=20types=20that=20are=20known=20to=20have=0A+=20*=20= inconsistent=20on-disk=20representations=20across=20server=20versions.=0A= +=20*=0A+=20*=20We=20check=20for=20the=20type(s)=20in=20tables,=20= matviews,=20and=20indexes,=20but=20not=20views;=0A+=20*=20there's=20no=20= storage=20involved=20in=20a=20view.=0A+=20*/=0A+static=20void=0A= +check_for_data_types_usage(ClusterInfo=20*cluster,=20= DataTypesUsageChecks=20*=20checks)=0A+{=0A+=09bool=09=09found=20=3D=20= false;=0A+=09bool=09=20=20=20*results;=0A+=09PQExpBufferData=20report;=0A= +=09DataTypesUsageChecks=20*tmp=20=3D=20checks;=0A+=09int=09=09=09= n_data_types_usage_checks=20=3D=200;=0A+=0A+=09prep_status("Checking=20= for=20data=20type=20usage");=0A+=0A+=09/*=20Gather=20number=20of=20= checks=20to=20perform=20*/=0A+=09while=20(tmp->status=20!=3D=20NULL)=0A+=09= {=0A+=09=09n_data_types_usage_checks++;=0A+=09=09tmp++;=0A+=09}=0A+=0A+=09= /*=20Prepare=20an=20array=20to=20store=20the=20results=20of=20checks=20= in=20*/=0A+=09results=20=3D=20pg_malloc0(sizeof(bool)=20*=20= n_data_types_usage_checks);=0A+=0A+=09/*=0A+=09=20*=20Connect=20to=20= each=20database=20in=20the=20cluster=20and=20run=20all=20defined=20= checks=0A+=09=20*=20against=20that=20database=20before=20trying=20the=20= next=20one.=0A+=09=20*/=0A+=09for=20(int=20dbnum=20=3D=200;=20dbnum=20<=20= cluster->dbarr.ndbs;=20dbnum++)=0A+=09{=0A+=09=09DbInfo=09=20=20=20= *active_db=20=3D=20&cluster->dbarr.dbs[dbnum];=0A+=09=09PGconn=09=20=20=20= *conn=20=3D=20connectToServer(cluster,=20active_db->db_name);=0A+=0A+=09=09= for=20(int=20checknum=20=3D=200;=20checknum=20<=20= n_data_types_usage_checks;=20checknum++)=0A+=09=09{=0A+=09=09=09PGresult=20= =20=20*res;=0A+=09=09=09int=09=09=09ntups;=0A+=09=09=09int=09=09=09= i_nspname;=0A+=09=09=09int=09=09=09i_relname;=0A+=09=09=09int=09=09=09= i_attname;=0A+=09=09=09FILE=09=20=20=20*script=20=3D=20NULL;=0A+=09=09=09= bool=09=09db_used=20=3D=20false;=0A+=09=09=09char=09=09= output_path[MAXPGPATH];=0A+=09=09=09DataTypesUsageChecks=20*cur_check=20= =3D=20&checks[checknum];=0A+=0A+=09=09=09if=20= (cur_check->threshold_version=20=3D=3D=20MANUAL_CHECK)=0A+=09=09=09{=0A+=09= =09=09=09Assert(cur_check->version_hook);=0A+=0A+=09=09=09=09/*=0A+=09=09= =09=09=20*=20Make=20sure=20that=20the=20check=20applies=20to=20the=20= current=20cluster=0A+=09=09=09=09=20*=20version=20and=20skip=20if=20not.=20= If=20no=20check=20hook=20has=20been=20defined=0A+=09=09=09=09=20*=20we=20= run=20the=20check=20for=20all=20versions.=0A+=09=09=09=09=20*/=0A+=09=09=09= =09if=20(!cur_check->version_hook(cluster))=0A+=09=09=09=09=09continue;=0A= +=09=09=09}=0A+=09=09=09else=20if=20(cur_check->threshold_version=20!=3D=20= ALL_VERSIONS)=0A+=09=09=09{=0A+=09=09=09=09if=20= (GET_MAJOR_VERSION(cluster->major_version)=20>=20= cur_check->threshold_version)=0A+=09=09=09=09=09continue;=0A+=09=09=09}=0A= +=09=09=09else=0A+=09=09=09=09Assert(cur_check->threshold_version=20=3D=3D= =20ALL_VERSIONS);=0A+=0A+=09=09=09snprintf(output_path,=20= sizeof(output_path),=20"%s/%s",=0A+=09=09=09=09=09=20log_opts.basedir,=0A= +=09=09=09=09=09=20cur_check->report_filename);=0A+=0A+=09=09=09/*=0A+=09= =09=09=20*=20The=20type(s)=20of=20interest=20might=20be=20wrapped=20in=20= a=20domain,=20array,=0A+=09=09=09=20*=20composite,=20or=20range,=20and=20= these=20container=20types=20can=20be=20nested=0A+=09=09=09=20*=20(to=20= varying=20extents=20depending=20on=20server=20version,=20but=20that's=20= not=0A+=09=09=09=20*=20of=20concern=20here).=20=20To=20handle=20all=20= these=20cases=20we=20need=20a=0A+=09=09=09=20*=20recursive=20CTE.=0A+=09=09= =09=20*/=0A+=09=09=09res=20=3D=20executeQueryOrDie(conn,=0A+=09=09=09=09=09= =09=09=09=09"WITH=20RECURSIVE=20oids=20AS=20(=20"=0A+=09=09=09/*=20start=20= with=20the=20type(s)=20returned=20by=20base_query=20*/=0A+=09=09=09=09=09= =09=09=09=09"=09%s=20"=0A+=09=09=09=09=09=09=09=09=09"=09UNION=20ALL=20"=0A= +=09=09=09=09=09=09=09=09=09"=09SELECT=20*=20FROM=20(=20"=0A+=09=09=09/*=20= inner=20WITH=20because=20we=20can=20only=20reference=20the=20CTE=20once=20= */=0A+=09=09=09=09=09=09=09=09=09"=09=09WITH=20x=20AS=20(SELECT=20oid=20= FROM=20oids)=20"=0A+=09=09=09/*=20domains=20on=20any=20type=20selected=20= so=20far=20*/=0A+=09=09=09=09=09=09=09=09=09"=09=09=09SELECT=20t.oid=20= FROM=20pg_catalog.pg_type=20t,=20x=20WHERE=20typbasetype=20=3D=20x.oid=20= AND=20typtype=20=3D=20'd'=20"=0A+=09=09=09=09=09=09=09=09=09"=09=09=09= UNION=20ALL=20"=0A+=09=09=09/*=20arrays=20over=20any=20type=20selected=20= so=20far=20*/=0A+=09=09=09=09=09=09=09=09=09"=09=09=09SELECT=20t.oid=20= FROM=20pg_catalog.pg_type=20t,=20x=20WHERE=20typelem=20=3D=20x.oid=20AND=20= typtype=20=3D=20'b'=20"=0A+=09=09=09=09=09=09=09=09=09"=09=09=09UNION=20= ALL=20"=0A+=09=09=09/*=20composite=20types=20containing=20any=20type=20= selected=20so=20far=20*/=0A+=09=09=09=09=09=09=09=09=09"=09=09=09SELECT=20= t.oid=20FROM=20pg_catalog.pg_type=20t,=20pg_catalog.pg_class=20c,=20= pg_catalog.pg_attribute=20a,=20x=20"=0A+=09=09=09=09=09=09=09=09=09"=09=09= =09WHERE=20t.typtype=20=3D=20'c'=20AND=20"=0A+=09=09=09=09=09=09=09=09=09= "=09=09=09=09=20=20t.oid=20=3D=20c.reltype=20AND=20"=0A+=09=09=09=09=09=09= =09=09=09"=09=09=09=09=20=20c.oid=20=3D=20a.attrelid=20AND=20"=0A+=09=09=09= =09=09=09=09=09=09"=09=09=09=09=20=20NOT=20a.attisdropped=20AND=20"=0A+=09= =09=09=09=09=09=09=09=09"=09=09=09=09=20=20a.atttypid=20=3D=20x.oid=20"=0A= +=09=09=09=09=09=09=09=09=09"=09=09=09UNION=20ALL=20"=0A+=09=09=09/*=20= ranges=20containing=20any=20type=20selected=20so=20far=20*/=0A+=09=09=09=09= =09=09=09=09=09"=09=09=09SELECT=20t.oid=20FROM=20pg_catalog.pg_type=20t,=20= pg_catalog.pg_range=20r,=20x=20"=0A+=09=09=09=09=09=09=09=09=09"=09=09=09= WHERE=20t.typtype=20=3D=20'r'=20AND=20r.rngtypid=20=3D=20t.oid=20AND=20= r.rngsubtype=20=3D=20x.oid"=0A+=09=09=09=09=09=09=09=09=09"=09)=20foo=20= "=0A+=09=09=09=09=09=09=09=09=09")=20"=0A+=09=09=09/*=20now=20look=20for=20= stored=20columns=20of=20any=20such=20type=20*/=0A+=09=09=09=09=09=09=09=09= =09"SELECT=20n.nspname,=20c.relname,=20a.attname=20"=0A+=09=09=09=09=09=09= =09=09=09"FROM=09pg_catalog.pg_class=20c,=20"=0A+=09=09=09=09=09=09=09=09= =09"=09=09pg_catalog.pg_namespace=20n,=20"=0A+=09=09=09=09=09=09=09=09=09= "=09=09pg_catalog.pg_attribute=20a=20"=0A+=09=09=09=09=09=09=09=09=09= "WHERE=09c.oid=20=3D=20a.attrelid=20AND=20"=0A+=09=09=09=09=09=09=09=09=09= "=09=09NOT=20a.attisdropped=20AND=20"=0A+=09=09=09=09=09=09=09=09=09"=09=09= a.atttypid=20IN=20(SELECT=20oid=20FROM=20oids)=20AND=20"=0A+=09=09=09=09=09= =09=09=09=09"=09=09c.relkind=20IN=20("=0A+=09=09=09=09=09=09=09=09=09= CppAsString2(RELKIND_RELATION)=20",=20"=0A+=09=09=09=09=09=09=09=09=09= CppAsString2(RELKIND_MATVIEW)=20",=20"=0A+=09=09=09=09=09=09=09=09=09= CppAsString2(RELKIND_INDEX)=20")=20AND=20"=0A+=09=09=09=09=09=09=09=09=09= "=09=09c.relnamespace=20=3D=20n.oid=20AND=20"=0A+=09=09=09/*=20exclude=20= possible=20orphaned=20temp=20tables=20*/=0A+=09=09=09=09=09=09=09=09=09"=09= =09n.nspname=20!~=20'^pg_temp_'=20AND=20"=0A+=09=09=09=09=09=09=09=09=09= "=09=09n.nspname=20!~=20'^pg_toast_temp_'=20AND=20"=0A+=09=09=09/*=20= exclude=20system=20catalogs,=20too=20*/=0A+=09=09=09=09=09=09=09=09=09"=09= =09n.nspname=20NOT=20IN=20('pg_catalog',=20'information_schema')",=0A+=09= =09=09=09=09=09=09=09=09cur_check->base_query);=0A+=0A+=09=09=09ntups=20= =3D=20PQntuples(res);=0A+=0A+=09=09=09/*=0A+=09=09=09=20*=20The=20= datatype=20was=20found,=20so=20extract=20the=20data=20and=20log=20to=20= the=0A+=09=09=09=20*=20requested=20filename.=20We=20need=20to=20open=20= the=20file=20for=20appending=0A+=09=09=09=20*=20since=20the=20check=20= might=20have=20already=20found=20the=20type=20in=20another=0A+=09=09=09=20= *=20database=20earlier=20in=20the=20loop.=0A+=09=09=09=20*/=0A+=09=09=09= if=20(ntups)=0A+=09=09=09{=0A+=09=09=09=09/*=0A+=09=09=09=09=20*=20Make=20= sure=20we=20have=20a=20buffer=20to=20save=20reports=20to=20now=20that=20= we=0A+=09=09=09=09=20*=20found=20a=20first=20failing=20check.=0A+=09=09=09= =09=20*/=0A+=09=09=09=09if=20(!found)=0A+=09=09=09=09=09= initPQExpBuffer(&report);=0A+=09=09=09=09found=20=3D=20true;=0A+=0A+=09=09= =09=09/*=0A+=09=09=09=09=20*=20If=20this=20is=20the=20first=20time=20we=20= see=20an=20error=20for=20the=20check=20in=0A+=09=09=09=09=20*=20question=20= then=20print=20a=20status=20message=20of=20the=20failure.=0A+=09=09=09=09= =20*/=0A+=09=09=09=09if=20(!results[checknum])=0A+=09=09=09=09{=0A+=09=09= =09=09=09pg_log(PG_REPORT,=20"=20=20=20=20failed=20check:=20%s",=20= _(cur_check->status));=0A+=09=09=09=09=09appendPQExpBuffer(&report,=20= "\n%s\n%s=20=20=20=20%s\n",=0A+=09=09=09=09=09=09=09=09=09=20=20= _(cur_check->report_text),=0A+=09=09=09=09=09=09=09=09=09=20=20_("A=20= list=20of=20the=20problem=20columns=20is=20in=20the=20file:"),=0A+=09=09=09= =09=09=09=09=09=09=20=20output_path);=0A+=09=09=09=09}=0A+=09=09=09=09= results[checknum]=20=3D=20true;=0A+=0A+=09=09=09=09i_nspname=20=3D=20= PQfnumber(res,=20"nspname");=0A+=09=09=09=09i_relname=20=3D=20= PQfnumber(res,=20"relname");=0A+=09=09=09=09i_attname=20=3D=20= PQfnumber(res,=20"attname");=0A+=0A+=09=09=09=09for=20(int=20rowno=20=3D=20= 0;=20rowno=20<=20ntups;=20rowno++)=0A+=09=09=09=09{=0A+=09=09=09=09=09if=20= (script=20=3D=3D=20NULL=20&&=20(script=20=3D=20fopen_priv(output_path,=20= "a"))=20=3D=3D=20NULL)=0A+=09=09=09=09=09=09pg_fatal("could=20not=20open=20= file=20\"%s\":=20%s",=0A+=09=09=09=09=09=09=09=09=20output_path,=0A+=09=09= =09=09=09=09=09=09=20strerror(errno));=0A+=09=09=09=09=09if=20(!db_used)=0A= +=09=09=09=09=09{=0A+=09=09=09=09=09=09fprintf(script,=20"In=20database:=20= %s\n",=20active_db->db_name);=0A+=09=09=09=09=09=09db_used=20=3D=20true;=0A= +=09=09=09=09=09}=0A+=09=09=09=09=09fprintf(script,=20"=20=20= %s.%s.%s\n",=0A+=09=09=09=09=09=09=09PQgetvalue(res,=20rowno,=20= i_nspname),=0A+=09=09=09=09=09=09=09PQgetvalue(res,=20rowno,=20= i_relname),=0A+=09=09=09=09=09=09=09PQgetvalue(res,=20rowno,=20= i_attname));=0A+=09=09=09=09}=0A+=0A+=09=09=09=09if=20(script)=0A+=09=09=09= =09{=0A+=09=09=09=09=09fclose(script);=0A+=09=09=09=09=09script=20=3D=20= NULL;=0A+=09=09=09=09}=0A+=09=09=09}=0A+=0A+=09=09=09PQclear(res);=0A+=09= =09}=0A+=0A+=09=09PQfinish(conn);=0A+=09}=0A+=0A+=09if=20(found)=0A+=09=09= pg_fatal("Data=20type=20checks=20failed:=20%s",=20report.data);=0A+=0A+=09= check_ok();=0A+}=0A=20=0A=20/*=0A=20=20*=20fix_path_separator=0A@@=20= -110,8=20+596,6=20@@=20check_and_dump_old_cluster(bool=20live_check)=0A=20= =09check_is_install_user(&old_cluster);=0A=20=09= check_proper_datallowconn(&old_cluster);=0A=20=09= check_for_prepared_transactions(&old_cluster);=0A-=09= check_for_composite_data_type_usage(&old_cluster);=0A-=09= check_for_reg_data_type_usage(&old_cluster);=0A=20=09= check_for_isn_and_int8_passing_mismatch(&old_cluster);=0A=20=0A=20=09if=20= (GET_MAJOR_VERSION(old_cluster.major_version)=20>=3D=201700)=0A@@=20= -129,22=20+613,7=20@@=20check_and_dump_old_cluster(bool=20live_check)=0A=20= =09=09check_old_cluster_subscription_state();=0A=20=09}=0A=20=0A-=09/*=0A= -=09=20*=20PG=2016=20increased=20the=20size=20of=20the=20'aclitem'=20= type,=20which=20breaks=20the=0A-=09=20*=20on-disk=20format=20for=20= existing=20data.=0A-=09=20*/=0A-=09if=20= (GET_MAJOR_VERSION(old_cluster.major_version)=20<=3D=201500)=0A-=09=09= check_for_aclitem_data_type_usage(&old_cluster);=0A-=0A-=09/*=0A-=09=20*=20= PG=2012=20removed=20types=20abstime,=20reltime,=20tinterval.=0A-=09=20*/=0A= -=09if=20(GET_MAJOR_VERSION(old_cluster.major_version)=20<=3D=201100)=0A= -=09{=0A-=09=09check_for_removed_data_type_usage(&old_cluster,=20"12",=20= "abstime");=0A-=09=09check_for_removed_data_type_usage(&old_cluster,=20= "12",=20"reltime");=0A-=09=09= check_for_removed_data_type_usage(&old_cluster,=20"12",=20"tinterval");=0A= -=09}=0A+=09check_for_data_types_usage(&old_cluster,=20= data_types_usage_checks);=0A=20=0A=20=09/*=0A=20=09=20*=20PG=2014=20= changed=20the=20function=20signature=20of=20encoding=20conversion=20= functions.=0A@@=20-176,21=20+645,12=20@@=20= check_and_dump_old_cluster(bool=20live_check)=0A=20=09if=20= (GET_MAJOR_VERSION(old_cluster.major_version)=20<=3D=201100)=0A=20=09=09= check_for_tables_with_oids(&old_cluster);=0A=20=0A-=09/*=0A-=09=20*=20PG=20= 12=20changed=20the=20'sql_identifier'=20type=20storage=20to=20be=20based=20= on=20name,=0A-=09=20*=20not=20varchar,=20which=20breaks=20on-disk=20= format=20for=20existing=20data.=20So=20we=20need=0A-=09=20*=20to=20= prevent=20upgrade=20when=20used=20in=20user=20objects=20(tables,=20= indexes,=20...).=0A-=09=20*/=0A-=09if=20= (GET_MAJOR_VERSION(old_cluster.major_version)=20<=3D=201100)=0A-=09=09= old_11_check_for_sql_identifier_data_type_usage(&old_cluster);=0A-=0A=20=09= /*=0A=20=09=20*=20Pre-PG=2010=20allowed=20tables=20with=20'unknown'=20= type=20columns=20and=20non=20WAL=20logged=0A=20=09=20*=20hash=20indexes=0A= =20=09=20*/=0A=20=09if=20(GET_MAJOR_VERSION(old_cluster.major_version)=20= <=3D=20906)=0A=20=09{=0A-=09=09= old_9_6_check_for_unknown_data_type_usage(&old_cluster);=0A=20=09=09if=20= (user_opts.check)=0A=20=09=09=09= old_9_6_invalidate_hash_indexes(&old_cluster,=20true);=0A=20=09}=0A@@=20= -199,14=20+659,6=20@@=20check_and_dump_old_cluster(bool=20live_check)=0A=20= =09if=20(GET_MAJOR_VERSION(old_cluster.major_version)=20<=3D=20905)=0A=20= =09=09check_for_pg_role_prefix(&old_cluster);=0A=20=0A-=09if=20= (GET_MAJOR_VERSION(old_cluster.major_version)=20=3D=3D=20904=20&&=0A-=09=09= old_cluster.controldata.cat_ver=20<=20JSONB_FORMAT_CHANGE_CAT_VER)=0A-=09= =09check_for_jsonb_9_4_usage(&old_cluster);=0A-=0A-=09/*=20Pre-PG=209.4=20= had=20a=20different=20'line'=20data=20type=20internal=20format=20*/=0A-=09= if=20(GET_MAJOR_VERSION(old_cluster.major_version)=20<=3D=20903)=0A-=09=09= old_9_3_check_for_line_data_type_usage(&old_cluster);=0A-=0A=20=09/*=0A=20= =09=20*=20While=20not=20a=20check=20option,=20we=20do=20this=20now=20= because=20this=20is=20the=20only=20time=0A=20=09=20*=20the=20old=20= server=20is=20running.=0A@@=20-1124,220=20+1576,6=20@@=20= check_for_tables_with_oids(ClusterInfo=20*cluster)=0A=20}=0A=20=0A=20=0A= -/*=0A-=20*=20check_for_composite_data_type_usage()=0A-=20*=09Check=20= for=20system-defined=20composite=20types=20used=20in=20user=20tables.=0A= -=20*=0A-=20*=09The=20OIDs=20of=20rowtypes=20of=20system=20catalogs=20= and=20information_schema=20views=0A-=20*=09can=20change=20across=20major=20= versions;=20unlike=20user-defined=20types,=20we=20have=0A-=20*=09no=20= mechanism=20for=20forcing=20them=20to=20be=20the=20same=20in=20the=20new=20= cluster.=0A-=20*=09Hence,=20if=20any=20user=20table=20uses=20one,=20= that's=20problematic=20for=20pg_upgrade.=0A-=20*/=0A-static=20void=0A= -check_for_composite_data_type_usage(ClusterInfo=20*cluster)=0A-{=0A-=09= bool=09=09found;=0A-=09Oid=09=09=09firstUserOid;=0A-=09char=09=09= output_path[MAXPGPATH];=0A-=09char=09=20=20=20*base_query;=0A-=0A-=09= prep_status("Checking=20for=20system-defined=20composite=20types=20in=20= user=20tables");=0A-=0A-=09snprintf(output_path,=20sizeof(output_path),=20= "%s/%s",=0A-=09=09=09=20log_opts.basedir,=0A-=09=09=09=20= "tables_using_composite.txt");=0A-=0A-=09/*=0A-=09=20*=20Look=20for=20= composite=20types=20that=20were=20made=20during=20initdb=20*or*=20belong=20= to=0A-=09=20*=20information_schema;=20that's=20important=20in=20case=20= information_schema=20was=0A-=09=20*=20dropped=20and=20reloaded.=0A-=09=20= *=0A-=09=20*=20The=20cutoff=20OID=20here=20should=20match=20the=20source=20= cluster's=20value=20of=0A-=09=20*=20FirstNormalObjectId.=20=20We=20= hardcode=20it=20rather=20than=20using=20that=20C=20#define=0A-=09=20*=20= because,=20if=20that=20#define=20is=20ever=20changed,=20our=20own=20= version's=20value=20is=0A-=09=20*=20NOT=20what=20to=20use.=20=20= Eventually=20we=20may=20need=20a=20test=20on=20the=20source=20cluster's=0A= -=09=20*=20version=20to=20select=20the=20correct=20value.=0A-=09=20*/=0A= -=09firstUserOid=20=3D=2016384;=0A-=0A-=09base_query=20=3D=20= psprintf("SELECT=20t.oid=20FROM=20pg_catalog.pg_type=20t=20"=0A-=09=09=09= =09=09=09=20=20"LEFT=20JOIN=20pg_catalog.pg_namespace=20n=20ON=20= t.typnamespace=20=3D=20n.oid=20"=0A-=09=09=09=09=09=09=20=20"=20WHERE=20= typtype=20=3D=20'c'=20AND=20(t.oid=20<=20%u=20OR=20nspname=20=3D=20= 'information_schema')",=0A-=09=09=09=09=09=09=20=20firstUserOid);=0A-=0A= -=09found=20=3D=20check_for_data_types_usage(cluster,=20base_query,=20= output_path);=0A-=0A-=09free(base_query);=0A-=0A-=09if=20(found)=0A-=09{=0A= -=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09pg_fatal("Your=20= installation=20contains=20system-defined=20composite=20types=20in=20user=20= tables.\n"=0A-=09=09=09=09=20"These=20type=20OIDs=20are=20not=20stable=20= across=20PostgreSQL=20versions,\n"=0A-=09=09=09=09=20"so=20this=20= cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can\n"=0A-=09=09= =09=09=20"drop=20the=20problem=20columns=20and=20restart=20the=20= upgrade.\n"=0A-=09=09=09=09=20"A=20list=20of=20the=20problem=20columns=20= is=20in=20the=20file:\n"=0A-=09=09=09=09=20"=20=20=20=20%s",=20= output_path);=0A-=09}=0A-=09else=0A-=09=09check_ok();=0A-}=0A-=0A-/*=0A-=20= *=20check_for_reg_data_type_usage()=0A-=20*=09pg_upgrade=20only=20= preserves=20these=20system=20values:=0A-=20*=09=09pg_class.oid=0A-=20*=09= =09pg_type.oid=0A-=20*=09=09pg_enum.oid=0A-=20*=0A-=20*=09Many=20of=20= the=20reg*=20data=20types=20reference=20system=20catalog=20info=20that=20= is=0A-=20*=09not=20preserved,=20and=20hence=20these=20data=20types=20= cannot=20be=20used=20in=20user=0A-=20*=09tables=20upgraded=20by=20= pg_upgrade.=0A-=20*/=0A-static=20void=0A= -check_for_reg_data_type_usage(ClusterInfo=20*cluster)=0A-{=0A-=09bool=09= =09found;=0A-=09char=09=09output_path[MAXPGPATH];=0A-=0A-=09= prep_status("Checking=20for=20reg*=20data=20types=20in=20user=20= tables");=0A-=0A-=09snprintf(output_path,=20sizeof(output_path),=20= "%s/%s",=0A-=09=09=09=20log_opts.basedir,=0A-=09=09=09=20= "tables_using_reg.txt");=0A-=0A-=09/*=0A-=09=20*=20Note:=20older=20= servers=20will=20not=20have=20all=20of=20these=20reg*=20types,=20so=20we=20= have=0A-=09=20*=20to=20write=20the=20query=20like=20this=20rather=20than=20= depending=20on=20casts=20to=20regtype.=0A-=09=20*/=0A-=09found=20=3D=20= check_for_data_types_usage(cluster,=0A-=09=09=09=09=09=09=09=09=09=20=20=20= "SELECT=20oid=20FROM=20pg_catalog.pg_type=20t=20"=0A-=09=09=09=09=09=09=09= =09=09=20=20=20"WHERE=20t.typnamespace=20=3D=20"=0A-=09=09=09=09=09=09=09= =09=09=20=20=20"=20=20=20=20=20=20=20=20(SELECT=20oid=20FROM=20= pg_catalog.pg_namespace=20"=0A-=09=09=09=09=09=09=09=09=09=20=20=20"=20=20= =20=20=20=20=20=20=20WHERE=20nspname=20=3D=20'pg_catalog')=20"=0A-=09=09=09= =09=09=09=09=09=09=20=20=20"=20=20AND=20t.typname=20IN=20(=20"=0A-=09/*=20= pg_class.oid=20is=20preserved,=20so=20'regclass'=20is=20OK=20*/=0A-=09=09= =09=09=09=09=09=09=09=20=20=20"=20=20=20=20=20=20=20=20=20=20=20= 'regcollation',=20"=0A-=09=09=09=09=09=09=09=09=09=20=20=20"=20=20=20=20=20= =20=20=20=20=20=20'regconfig',=20"=0A-=09=09=09=09=09=09=09=09=09=20=20=20= "=20=20=20=20=20=20=20=20=20=20=20'regdictionary',=20"=0A-=09=09=09=09=09= =09=09=09=09=20=20=20"=20=20=20=20=20=20=20=20=20=20=20'regnamespace',=20= "=0A-=09=09=09=09=09=09=09=09=09=20=20=20"=20=20=20=20=20=20=20=20=20=20=20= 'regoper',=20"=0A-=09=09=09=09=09=09=09=09=09=20=20=20"=20=20=20=20=20=20= =20=20=20=20=20'regoperator',=20"=0A-=09=09=09=09=09=09=09=09=09=20=20=20= "=20=20=20=20=20=20=20=20=20=20=20'regproc',=20"=0A-=09=09=09=09=09=09=09= =09=09=20=20=20"=20=20=20=20=20=20=20=20=20=20=20'regprocedure'=20"=0A-=09= /*=20pg_authid.oid=20is=20preserved,=20so=20'regrole'=20is=20OK=20*/=0A-=09= /*=20pg_type.oid=20is=20(mostly)=20preserved,=20so=20'regtype'=20is=20OK=20= */=0A-=09=09=09=09=09=09=09=09=09=20=20=20"=20=20=20=20=20=20=20=20=20= )",=0A-=09=09=09=09=09=09=09=09=09=20=20=20output_path);=0A-=0A-=09if=20= (found)=0A-=09{=0A-=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09= pg_fatal("Your=20installation=20contains=20one=20of=20the=20reg*=20data=20= types=20in=20user=20tables.\n"=0A-=09=09=09=09=20"These=20data=20types=20= reference=20system=20OIDs=20that=20are=20not=20preserved=20by\n"=0A-=09=09= =09=09=20"pg_upgrade,=20so=20this=20cluster=20cannot=20currently=20be=20= upgraded.=20=20You=20can\n"=0A-=09=09=09=09=20"drop=20the=20problem=20= columns=20and=20restart=20the=20upgrade.\n"=0A-=09=09=09=09=20"A=20list=20= of=20the=20problem=20columns=20is=20in=20the=20file:\n"=0A-=09=09=09=09=20= "=20=20=20=20%s",=20output_path);=0A-=09}=0A-=09else=0A-=09=09= check_ok();=0A-}=0A-=0A-/*=0A-=20*=20check_for_aclitem_data_type_usage=0A= -=20*=0A-=20*=09aclitem=20changed=20its=20storage=20format=20in=2016,=20= so=20check=20for=20it.=0A-=20*/=0A-static=20void=0A= -check_for_aclitem_data_type_usage(ClusterInfo=20*cluster)=0A-{=0A-=09= char=09=09output_path[MAXPGPATH];=0A-=0A-=09prep_status("Checking=20for=20= incompatible=20\"%s\"=20data=20type=20in=20user=20tables",=0A-=09=09=09=09= "aclitem");=0A-=0A-=09snprintf(output_path,=20sizeof(output_path),=20= "tables_using_aclitem.txt");=0A-=0A-=09if=20= (check_for_data_type_usage(cluster,=20"pg_catalog.aclitem",=20= output_path))=0A-=09{=0A-=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09= pg_fatal("Your=20installation=20contains=20the=20\"aclitem\"=20data=20= type=20in=20user=20tables.\n"=0A-=09=09=09=09=20"The=20internal=20format=20= of=20\"aclitem\"=20changed=20in=20PostgreSQL=20version=2016\n"=0A-=09=09=09= =09=20"so=20this=20cluster=20cannot=20currently=20be=20upgraded.=20=20= You=20can=20drop=20the\n"=0A-=09=09=09=09=20"problem=20columns=20and=20= restart=20the=20upgrade.=20=20A=20list=20of=20the=20problem\n"=0A-=09=09=09= =09=20"columns=20is=20in=20the=20file:\n"=0A-=09=09=09=09=20"=20=20=20=20= %s",=20output_path);=0A-=09}=0A-=09else=0A-=09=09check_ok();=0A-}=0A-=0A= -/*=0A-=20*=20check_for_removed_data_type_usage=0A-=20*=0A-=20*=09Check=20= for=20in-core=20data=20types=20that=20have=20been=20removed.=20=20= Callers=20know=0A-=20*=09the=20exact=20list.=0A-=20*/=0A-static=20void=0A= -check_for_removed_data_type_usage(ClusterInfo=20*cluster,=20const=20= char=20*version,=0A-=09=09=09=09=09=09=09=09=20=20const=20char=20= *datatype)=0A-{=0A-=09char=09=09output_path[MAXPGPATH];=0A-=09char=09=09= typename[NAMEDATALEN];=0A-=0A-=09prep_status("Checking=20for=20removed=20= \"%s\"=20data=20type=20in=20user=20tables",=0A-=09=09=09=09datatype);=0A= -=0A-=09snprintf(output_path,=20sizeof(output_path),=20= "tables_using_%s.txt",=0A-=09=09=09=20datatype);=0A-=09= snprintf(typename,=20sizeof(typename),=20"pg_catalog.%s",=20datatype);=0A= -=0A-=09if=20(check_for_data_type_usage(cluster,=20typename,=20= output_path))=0A-=09{=0A-=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09= pg_fatal("Your=20installation=20contains=20the=20\"%s\"=20data=20type=20= in=20user=20tables.\n"=0A-=09=09=09=09=20"The=20\"%s\"=20type=20has=20= been=20removed=20in=20PostgreSQL=20version=20%s,\n"=0A-=09=09=09=09=20= "so=20this=20cluster=20cannot=20currently=20be=20upgraded.=20=20You=20= can=20drop=20the\n"=0A-=09=09=09=09=20"problem=20columns,=20or=20change=20= them=20to=20another=20data=20type,=20and=20restart\n"=0A-=09=09=09=09=20= "the=20upgrade.=20=20A=20list=20of=20the=20problem=20columns=20is=20in=20= the=20file:\n"=0A-=09=09=09=09=20"=20=20=20=20%s",=20datatype,=20= datatype,=20version,=20output_path);=0A-=09}=0A-=09else=0A-=09=09= check_ok();=0A-}=0A-=0A-=0A-/*=0A-=20*=20check_for_jsonb_9_4_usage()=0A-=20= *=0A-=20*=09JSONB=20changed=20its=20storage=20format=20during=209.4=20= beta,=20so=20check=20for=20it.=0A-=20*/=0A-static=20void=0A= -check_for_jsonb_9_4_usage(ClusterInfo=20*cluster)=0A-{=0A-=09char=09=09= output_path[MAXPGPATH];=0A-=0A-=09prep_status("Checking=20for=20= incompatible=20\"jsonb\"=20data=20type");=0A-=0A-=09= snprintf(output_path,=20sizeof(output_path),=20"%s/%s",=0A-=09=09=09=20= log_opts.basedir,=0A-=09=09=09=20"tables_using_jsonb.txt");=0A-=0A-=09if=20= (check_for_data_type_usage(cluster,=20"pg_catalog.jsonb",=20= output_path))=0A-=09{=0A-=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09= pg_fatal("Your=20installation=20contains=20the=20\"jsonb\"=20data=20type=20= in=20user=20tables.\n"=0A-=09=09=09=09=20"The=20internal=20format=20of=20= \"jsonb\"=20changed=20during=209.4=20beta=20so=20this\n"=0A-=09=09=09=09=20= "cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can\n"=0A-=09=09= =09=09=20"drop=20the=20problem=20columns=20and=20restart=20the=20= upgrade.\n"=0A-=09=09=09=09=20"A=20list=20of=20the=20problem=20columns=20= is=20in=20the=20file:\n"=0A-=09=09=09=09=20"=20=20=20=20%s",=20= output_path);=0A-=09}=0A-=09else=0A-=09=09check_ok();=0A-}=0A-=0A=20/*=0A= =20=20*=20check_for_pg_role_prefix()=0A=20=20*=0Adiff=20--git=20= a/src/bin/pg_upgrade/pg_upgrade.h=20b/src/bin/pg_upgrade/pg_upgrade.h=0A= index=20d9a848cbfd..7daa67f809=20100644=0A---=20= a/src/bin/pg_upgrade/pg_upgrade.h=0A+++=20= b/src/bin/pg_upgrade/pg_upgrade.h=0A@@=20-351,6=20+351,9=20@@=20typedef=20= struct=0A=20}=20OSInfo;=0A=20=0A=20=0A+/*=20Function=20signature=20for=20= data=20type=20check=20version=20hook=20*/=0A+typedef=20bool=20= (*DataTypesUsageVersionCheck)=20(ClusterInfo=20*cluster);=0A+=0A=20/*=0A=20= =20*=20Global=20variables=0A=20=20*/=0A@@=20-475,18=20+478,10=20@@=20= unsigned=20int=20str2uint(const=20char=20*str);=0A=20=0A=20/*=20= version.c=20*/=0A=20=0A-bool=09=09check_for_data_types_usage(ClusterInfo=20= *cluster,=0A-=09=09=09=09=09=09=09=09=09=20=20=20const=20char=20= *base_query,=0A-=09=09=09=09=09=09=09=09=09=20=20=20const=20char=20= *output_path);=0A-bool=09=09check_for_data_type_usage(ClusterInfo=20= *cluster,=0A-=09=09=09=09=09=09=09=09=09=20=20const=20char=20*type_name,=0A= -=09=09=09=09=09=09=09=09=09=20=20const=20char=20*output_path);=0A-void=09= =09old_9_3_check_for_line_data_type_usage(ClusterInfo=20*cluster);=0A= -void=09=09old_9_6_check_for_unknown_data_type_usage(ClusterInfo=20= *cluster);=0A+bool=09=09jsonb_9_4_check_applicable(ClusterInfo=20= *cluster);=0A=20void=09=09old_9_6_invalidate_hash_indexes(ClusterInfo=20= *cluster,=0A=20=09=09=09=09=09=09=09=09=09=09=09bool=20check_mode);=0A=20= =0A-void=09=09= old_11_check_for_sql_identifier_data_type_usage(ClusterInfo=20*cluster);=0A= =20void=09=09report_extension_updates(ClusterInfo=20*cluster);=0A=20=0A=20= /*=20parallel.c=20*/=0Adiff=20--git=20a/src/bin/pg_upgrade/version.c=20= b/src/bin/pg_upgrade/version.c=0Aindex=2013b2c0f012..e3c7b4109c=20100644=0A= ---=20a/src/bin/pg_upgrade/version.c=0A+++=20= b/src/bin/pg_upgrade/version.c=0A@@=20-9,236=20+9,23=20@@=0A=20=0A=20= #include=20"postgres_fe.h"=0A=20=0A-#include=20"catalog/pg_class_d.h"=0A=20= #include=20"fe_utils/string_utils.h"=0A=20#include=20"pg_upgrade.h"=0A=20= =0A-=0A-/*=0A-=20*=20check_for_data_types_usage()=0A-=20*=09Detect=20= whether=20there=20are=20any=20stored=20columns=20depending=20on=20given=20= type(s)=0A-=20*=0A-=20*=20If=20so,=20write=20a=20report=20to=20the=20= given=20file=20name,=20and=20return=20true.=0A-=20*=0A-=20*=20base_query=20= should=20be=20a=20SELECT=20yielding=20a=20single=20column=20named=20= "oid",=0A-=20*=20containing=20the=20pg_type=20OIDs=20of=20one=20or=20= more=20types=20that=20are=20known=20to=20have=0A-=20*=20inconsistent=20= on-disk=20representations=20across=20server=20versions.=0A-=20*=0A-=20*=20= We=20check=20for=20the=20type(s)=20in=20tables,=20matviews,=20and=20= indexes,=20but=20not=20views;=0A-=20*=20there's=20no=20storage=20= involved=20in=20a=20view.=0A-=20*/=0A-bool=0A= -check_for_data_types_usage(ClusterInfo=20*cluster,=0A-=09=09=09=09=09=09= =20=20=20const=20char=20*base_query,=0A-=09=09=09=09=09=09=20=20=20const=20= char=20*output_path)=0A-{=0A-=09bool=09=09found=20=3D=20false;=0A-=09= FILE=09=20=20=20*script=20=3D=20NULL;=0A-=09int=09=09=09dbnum;=0A-=0A-=09= for=20(dbnum=20=3D=200;=20dbnum=20<=20cluster->dbarr.ndbs;=20dbnum++)=0A= -=09{=0A-=09=09DbInfo=09=20=20=20*active_db=20=3D=20= &cluster->dbarr.dbs[dbnum];=0A-=09=09PGconn=09=20=20=20*conn=20=3D=20= connectToServer(cluster,=20active_db->db_name);=0A-=09=09PQExpBufferData=20= querybuf;=0A-=09=09PGresult=20=20=20*res;=0A-=09=09bool=09=09db_used=20=3D= =20false;=0A-=09=09int=09=09=09ntups;=0A-=09=09int=09=09=09rowno;=0A-=09=09= int=09=09=09i_nspname,=0A-=09=09=09=09=09i_relname,=0A-=09=09=09=09=09= i_attname;=0A-=0A-=09=09/*=0A-=09=09=20*=20The=20type(s)=20of=20interest=20= might=20be=20wrapped=20in=20a=20domain,=20array,=0A-=09=09=20*=20= composite,=20or=20range,=20and=20these=20container=20types=20can=20be=20= nested=20(to=0A-=09=09=20*=20varying=20extents=20depending=20on=20server=20= version,=20but=20that's=20not=20of=0A-=09=09=20*=20concern=20here).=20=20= To=20handle=20all=20these=20cases=20we=20need=20a=20recursive=20CTE.=0A-=09= =09=20*/=0A-=09=09initPQExpBuffer(&querybuf);=0A-=09=09= appendPQExpBuffer(&querybuf,=0A-=09=09=09=09=09=09=20=20"WITH=20= RECURSIVE=20oids=20AS=20(=20"=0A-=09=09/*=20start=20with=20the=20type(s)=20= returned=20by=20base_query=20*/=0A-=09=09=09=09=09=09=20=20"=09%s=20"=0A= -=09=09=09=09=09=09=20=20"=09UNION=20ALL=20"=0A-=09=09=09=09=09=09=20=20= "=09SELECT=20*=20FROM=20(=20"=0A-=09=09/*=20inner=20WITH=20because=20we=20= can=20only=20reference=20the=20CTE=20once=20*/=0A-=09=09=09=09=09=09=20=20= "=09=09WITH=20x=20AS=20(SELECT=20oid=20FROM=20oids)=20"=0A-=09=09/*=20= domains=20on=20any=20type=20selected=20so=20far=20*/=0A-=09=09=09=09=09=09= =20=20"=09=09=09SELECT=20t.oid=20FROM=20pg_catalog.pg_type=20t,=20x=20= WHERE=20typbasetype=20=3D=20x.oid=20AND=20typtype=20=3D=20'd'=20"=0A-=09=09= =09=09=09=09=20=20"=09=09=09UNION=20ALL=20"=0A-=09=09/*=20arrays=20over=20= any=20type=20selected=20so=20far=20*/=0A-=09=09=09=09=09=09=20=20"=09=09=09= SELECT=20t.oid=20FROM=20pg_catalog.pg_type=20t,=20x=20WHERE=20typelem=20= =3D=20x.oid=20AND=20typtype=20=3D=20'b'=20"=0A-=09=09=09=09=09=09=20=20"=09= =09=09UNION=20ALL=20"=0A-=09=09/*=20composite=20types=20containing=20any=20= type=20selected=20so=20far=20*/=0A-=09=09=09=09=09=09=20=20"=09=09=09= SELECT=20t.oid=20FROM=20pg_catalog.pg_type=20t,=20pg_catalog.pg_class=20= c,=20pg_catalog.pg_attribute=20a,=20x=20"=0A-=09=09=09=09=09=09=20=20"=09= =09=09WHERE=20t.typtype=20=3D=20'c'=20AND=20"=0A-=09=09=09=09=09=09=20=20= "=09=09=09=09=20=20t.oid=20=3D=20c.reltype=20AND=20"=0A-=09=09=09=09=09=09= =20=20"=09=09=09=09=20=20c.oid=20=3D=20a.attrelid=20AND=20"=0A-=09=09=09=09= =09=09=20=20"=09=09=09=09=20=20NOT=20a.attisdropped=20AND=20"=0A-=09=09=09= =09=09=09=20=20"=09=09=09=09=20=20a.atttypid=20=3D=20x.oid=20"=0A-=09=09=09= =09=09=09=20=20"=09=09=09UNION=20ALL=20"=0A-=09=09/*=20ranges=20= containing=20any=20type=20selected=20so=20far=20*/=0A-=09=09=09=09=09=09=20= =20"=09=09=09SELECT=20t.oid=20FROM=20pg_catalog.pg_type=20t,=20= pg_catalog.pg_range=20r,=20x=20"=0A-=09=09=09=09=09=09=20=20"=09=09=09= WHERE=20t.typtype=20=3D=20'r'=20AND=20r.rngtypid=20=3D=20t.oid=20AND=20= r.rngsubtype=20=3D=20x.oid"=0A-=09=09=09=09=09=09=20=20"=09)=20foo=20"=0A= -=09=09=09=09=09=09=20=20")=20"=0A-=09=09/*=20now=20look=20for=20stored=20= columns=20of=20any=20such=20type=20*/=0A-=09=09=09=09=09=09=20=20"SELECT=20= n.nspname,=20c.relname,=20a.attname=20"=0A-=09=09=09=09=09=09=20=20"FROM=09= pg_catalog.pg_class=20c,=20"=0A-=09=09=09=09=09=09=20=20"=09=09= pg_catalog.pg_namespace=20n,=20"=0A-=09=09=09=09=09=09=20=20"=09=09= pg_catalog.pg_attribute=20a=20"=0A-=09=09=09=09=09=09=20=20"WHERE=09= c.oid=20=3D=20a.attrelid=20AND=20"=0A-=09=09=09=09=09=09=20=20"=09=09NOT=20= a.attisdropped=20AND=20"=0A-=09=09=09=09=09=09=20=20"=09=09a.atttypid=20= IN=20(SELECT=20oid=20FROM=20oids)=20AND=20"=0A-=09=09=09=09=09=09=20=20"=09= =09c.relkind=20IN=20("=0A-=09=09=09=09=09=09=20=20= CppAsString2(RELKIND_RELATION)=20",=20"=0A-=09=09=09=09=09=09=20=20= CppAsString2(RELKIND_MATVIEW)=20",=20"=0A-=09=09=09=09=09=09=20=20= CppAsString2(RELKIND_INDEX)=20")=20AND=20"=0A-=09=09=09=09=09=09=20=20"=09= =09c.relnamespace=20=3D=20n.oid=20AND=20"=0A-=09=09/*=20exclude=20= possible=20orphaned=20temp=20tables=20*/=0A-=09=09=09=09=09=09=20=20"=09=09= n.nspname=20!~=20'^pg_temp_'=20AND=20"=0A-=09=09=09=09=09=09=20=20"=09=09= n.nspname=20!~=20'^pg_toast_temp_'=20AND=20"=0A-=09=09/*=20exclude=20= system=20catalogs,=20too=20*/=0A-=09=09=09=09=09=09=20=20"=09=09= n.nspname=20NOT=20IN=20('pg_catalog',=20'information_schema')",=0A-=09=09= =09=09=09=09=20=20base_query);=0A-=0A-=09=09res=20=3D=20= executeQueryOrDie(conn,=20"%s",=20querybuf.data);=0A-=0A-=09=09ntups=20=3D= =20PQntuples(res);=0A-=09=09i_nspname=20=3D=20PQfnumber(res,=20= "nspname");=0A-=09=09i_relname=20=3D=20PQfnumber(res,=20"relname");=0A-=09= =09i_attname=20=3D=20PQfnumber(res,=20"attname");=0A-=09=09for=20(rowno=20= =3D=200;=20rowno=20<=20ntups;=20rowno++)=0A-=09=09{=0A-=09=09=09found=20= =3D=20true;=0A-=09=09=09if=20(script=20=3D=3D=20NULL=20&&=20(script=20=3D=20= fopen_priv(output_path,=20"w"))=20=3D=3D=20NULL)=0A-=09=09=09=09= pg_fatal("could=20not=20open=20file=20\"%s\":=20%s",=20output_path,=0A-=09= =09=09=09=09=09=20strerror(errno));=0A-=09=09=09if=20(!db_used)=0A-=09=09= =09{=0A-=09=09=09=09fprintf(script,=20"In=20database:=20%s\n",=20= active_db->db_name);=0A-=09=09=09=09db_used=20=3D=20true;=0A-=09=09=09}=0A= -=09=09=09fprintf(script,=20"=20=20%s.%s.%s\n",=0A-=09=09=09=09=09= PQgetvalue(res,=20rowno,=20i_nspname),=0A-=09=09=09=09=09PQgetvalue(res,=20= rowno,=20i_relname),=0A-=09=09=09=09=09PQgetvalue(res,=20rowno,=20= i_attname));=0A-=09=09}=0A-=0A-=09=09PQclear(res);=0A-=0A-=09=09= termPQExpBuffer(&querybuf);=0A-=0A-=09=09PQfinish(conn);=0A-=09}=0A-=0A-=09= if=20(script)=0A-=09=09fclose(script);=0A-=0A-=09return=20found;=0A-}=0A= -=0A=20/*=0A-=20*=20check_for_data_type_usage()=0A-=20*=09Detect=20= whether=20there=20are=20any=20stored=20columns=20depending=20on=20the=20= given=20type=0A-=20*=0A-=20*=20If=20so,=20write=20a=20report=20to=20the=20= given=20file=20name,=20and=20return=20true.=0A-=20*=0A-=20*=20type_name=20= should=20be=20a=20fully=20qualified=20type=20name.=20=20This=20is=20just=20= a=0A-=20*=20trivial=20wrapper=20around=20check_for_data_types_usage()=20= to=20convert=20a=0A-=20*=20type=20name=20into=20a=20base=20query.=0A+=20= *=20version_hook=20functions=20for=20check_for_data_types_usage=20in=20= order=20to=20determine=0A+=20*=20whether=20a=20data=20type=20check=20= should=20be=20executed=20for=20the=20cluster=20in=20question=20or=0A+=20= *=20not.=0A=20=20*/=0A=20bool=0A-check_for_data_type_usage(ClusterInfo=20= *cluster,=0A-=09=09=09=09=09=09=20=20const=20char=20*type_name,=0A-=09=09= =09=09=09=09=20=20const=20char=20*output_path)=0A= +jsonb_9_4_check_applicable(ClusterInfo=20*cluster)=0A=20{=0A-=09bool=09=09= found;=0A-=09char=09=20=20=20*base_query;=0A-=0A-=09base_query=20=3D=20= psprintf("SELECT=20'%s'::pg_catalog.regtype=20AS=20oid",=0A-=09=09=09=09=09= =09=20=20type_name);=0A+=09/*=20JSONB=20changed=20its=20storage=20format=20= during=209.4=20beta=20*/=0A+=09if=20= (GET_MAJOR_VERSION(cluster->major_version)=20=3D=3D=20904=20&&=0A+=09=09= cluster->controldata.cat_ver=20<=20JSONB_FORMAT_CHANGE_CAT_VER)=0A+=09=09= return=20true;=0A=20=0A-=09found=20=3D=20= check_for_data_types_usage(cluster,=20base_query,=20output_path);=0A-=0A= -=09free(base_query);=0A-=0A-=09return=20found;=0A-}=0A-=0A-=0A-/*=0A-=20= *=20old_9_3_check_for_line_data_type_usage()=0A-=20*=099.3=20->=209.4=0A= -=20*=09Fully=20implement=20the=20'line'=20data=20type=20in=209.4,=20= which=20previously=20returned=0A-=20*=09"not=20enabled"=20by=20default=20= and=20was=20only=20functionally=20enabled=20with=20a=0A-=20*=09= compile-time=20switch;=20as=20of=209.4=20"line"=20has=20a=20different=20= on-disk=0A-=20*=09representation=20format.=0A-=20*/=0A-void=0A= -old_9_3_check_for_line_data_type_usage(ClusterInfo=20*cluster)=0A-{=0A-=09= char=09=09output_path[MAXPGPATH];=0A-=0A-=09prep_status("Checking=20for=20= incompatible=20\"line\"=20data=20type");=0A-=0A-=09snprintf(output_path,=20= sizeof(output_path),=20"%s/%s",=0A-=09=09=09=20log_opts.basedir,=0A-=09=09= =09=20"tables_using_line.txt");=0A-=0A-=09if=20= (check_for_data_type_usage(cluster,=20"pg_catalog.line",=20output_path))=0A= -=09{=0A-=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09pg_fatal("Your=20= installation=20contains=20the=20\"line\"=20data=20type=20in=20user=20= tables.\n"=0A-=09=09=09=09=20"This=20data=20type=20changed=20its=20= internal=20and=20input/output=20format\n"=0A-=09=09=09=09=20"between=20= your=20old=20and=20new=20versions=20so=20this\n"=0A-=09=09=09=09=20= "cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can\n"=0A-=09=09= =09=09=20"drop=20the=20problem=20columns=20and=20restart=20the=20= upgrade.\n"=0A-=09=09=09=09=20"A=20list=20of=20the=20problem=20columns=20= is=20in=20the=20file:\n"=0A-=09=09=09=09=20"=20=20=20=20%s",=20= output_path);=0A-=09}=0A-=09else=0A-=09=09check_ok();=0A-}=0A-=0A-=0A-/*=0A= -=20*=20old_9_6_check_for_unknown_data_type_usage()=0A-=20*=099.6=20->=20= 10=0A-=20*=09It's=20no=20longer=20allowed=20to=20create=20tables=20or=20= views=20with=20"unknown"-type=0A-=20*=09columns.=20=20We=20do=20not=20= complain=20about=20views=20with=20such=20columns,=20because=0A-=20*=09= they=20should=20get=20silently=20converted=20to=20"text"=20columns=20= during=20the=20DDL=0A-=20*=09dump=20and=20reload;=20it=20seems=20= unlikely=20to=20be=20worth=20making=20users=20do=20that=0A-=20*=09by=20= hand.=20=20However,=20if=20there's=20a=20table=20with=20such=20a=20= column,=20the=20DDL=0A-=20*=09reload=20will=20fail,=20so=20we=20should=20= pre-detect=20that=20rather=20than=20failing=0A-=20*=09mid-upgrade.=20=20= Worse,=20if=20there's=20a=20matview=20with=20such=20a=20column,=20the=0A= -=20*=09DDL=20reload=20will=20silently=20change=20it=20to=20"text"=20= which=20won't=20match=20the=0A-=20*=09on-disk=20storage=20(which=20is=20= like=20"cstring").=20=20So=20we=20*must*=20reject=20that.=0A-=20*/=0A= -void=0A-old_9_6_check_for_unknown_data_type_usage(ClusterInfo=20= *cluster)=0A-{=0A-=09char=09=09output_path[MAXPGPATH];=0A-=0A-=09= prep_status("Checking=20for=20invalid=20\"unknown\"=20user=20columns");=0A= -=0A-=09snprintf(output_path,=20sizeof(output_path),=20"%s/%s",=0A-=09=09= =09=20log_opts.basedir,=0A-=09=09=09=20"tables_using_unknown.txt");=0A-=0A= -=09if=20(check_for_data_type_usage(cluster,=20"pg_catalog.unknown",=20= output_path))=0A-=09{=0A-=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09= pg_fatal("Your=20installation=20contains=20the=20\"unknown\"=20data=20= type=20in=20user=20tables.\n"=0A-=09=09=09=09=20"This=20data=20type=20is=20= no=20longer=20allowed=20in=20tables,=20so=20this\n"=0A-=09=09=09=09=20= "cluster=20cannot=20currently=20be=20upgraded.=20=20You=20can\n"=0A-=09=09= =09=09=20"drop=20the=20problem=20columns=20and=20restart=20the=20= upgrade.\n"=0A-=09=09=09=09=20"A=20list=20of=20the=20problem=20columns=20= is=20in=20the=20file:\n"=0A-=09=09=09=09=20"=20=20=20=20%s",=20= output_path);=0A-=09}=0A-=09else=0A-=09=09check_ok();=0A+=09return=20= false;=0A=20}=0A=20=0A=20/*=0A@@=20-353,41=20+140,6=20@@=20= old_9_6_invalidate_hash_indexes(ClusterInfo=20*cluster,=20bool=20= check_mode)=0A=20=09=09check_ok();=0A=20}=0A=20=0A-/*=0A-=20*=20= old_11_check_for_sql_identifier_data_type_usage()=0A-=20*=0911=20->=2012=0A= -=20*=09In=2012,=20the=20sql_identifier=20data=20type=20was=20switched=20= from=20name=20to=20varchar,=0A-=20*=09which=20does=20affect=20the=20= storage=20(name=20is=20by-ref,=20but=20not=20varlena).=20This=0A-=20*=09= means=20user=20tables=20using=20sql_identifier=20for=20columns=20are=20= broken=20because=0A-=20*=09the=20on-disk=20format=20is=20different.=0A-=20= */=0A-void=0A= -old_11_check_for_sql_identifier_data_type_usage(ClusterInfo=20*cluster)=0A= -{=0A-=09char=09=09output_path[MAXPGPATH];=0A-=0A-=09= prep_status("Checking=20for=20invalid=20\"sql_identifier\"=20user=20= columns");=0A-=0A-=09snprintf(output_path,=20sizeof(output_path),=20= "%s/%s",=0A-=09=09=09=20log_opts.basedir,=0A-=09=09=09=20= "tables_using_sql_identifier.txt");=0A-=0A-=09if=20= (check_for_data_type_usage(cluster,=20= "information_schema.sql_identifier",=0A-=09=09=09=09=09=09=09=09=20=20= output_path))=0A-=09{=0A-=09=09pg_log(PG_REPORT,=20"fatal");=0A-=09=09= pg_fatal("Your=20installation=20contains=20the=20\"sql_identifier\"=20= data=20type=20in=20user=20tables.\n"=0A-=09=09=09=09=20"The=20on-disk=20= format=20for=20this=20data=20type=20has=20changed,=20so=20this\n"=0A-=09=09= =09=09=20"cluster=20cannot=20currently=20be=20upgraded.=20=20You=20= can\n"=0A-=09=09=09=09=20"drop=20the=20problem=20columns=20and=20restart=20= the=20upgrade.\n"=0A-=09=09=09=09=20"A=20list=20of=20the=20problem=20= columns=20is=20in=20the=20file:\n"=0A-=09=09=09=09=20"=20=20=20=20%s",=20= output_path);=0A-=09}=0A-=09else=0A-=09=09check_ok();=0A-}=0A-=0A-=0A=20= /*=0A=20=20*=20report_extension_updates()=0A=20=20*=09Report=20= extensions=20that=20should=20be=20updated.=0A--=20=0A2.32.1=20(Apple=20= Git-133)=0A=0A= --Apple-Mail=_5F4F1FED-341B-4064-804D-6FC9A45CCB46--