From: David Modica <davidmo@imaginesoftware.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: RE: binary upgade errors
Thread-Topic: binary upgade errors
Thread-Index: AdQD9uZFYcc2bULDSy+FhZmBASKrMgAULQcAAL171rA=
Date: Mon, 18 Jun 2018 19:53:26 +0000
Message-ID: <0c70b27484974f60aae44d91101893a0@imaginesoftware.com>
References: <7cb957dd12774a52ac8a680b73910c5b@imaginesoftware.com>
 <10537.1529007868@sss.pgh.pa.us>
In-Reply-To: <10537.1529007868@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

tom,

thank you so much for your reply.

isn't 0::uint1 casting an int as uint and '0'::uint1 is casting a text as a=
 uint1 ?

not sure why one doesn't require a run time cast and the other doesn't. wou=
ld you

be able to explain to me ? I am sorry but this casting is new for me.

thanks,

david


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>=20
Sent: Thursday, June 14, 2018 4:24 PM
To: David Modica <davidmo@imaginesoftware.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: binary upgade errors

David Modica <davidmo@imaginesoftware.com> writes:
> I have been unsuccessfully trying to use pg_upgrade to upgrade from 9.6 t=
o 10.4.
> we have the uint extension in some of the databases. a combination of=20
> that extension and probably how we have used it is causing the upgrade to=
 fail. I will include the error msg.

> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 4538; 1247 280489=20
> DOMAIN bool_t postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  cannot cast =
type integer to public.uint1
>     Command was:
> -- For binary upgrade, must preserve pg_type oid SELECT=20
> pg_catalog.binary_upgrade_set_next_pg_type_oid('280489'::pg_catalog.oi
> d);

> CREATE DOMAIN "its"."bool_t" AS "public"."uint1" DEFAULT=20
> (0)::"public"."uint1";

Hmm ... it looks like this domain is depending on there to be a cast from i=
nteger to uint1, but that hasn't been created yet.  There may be a bug here=
; I'm not sure why pg_dump didn't delay dumping the domain to after it'd du=
mped the cast.  In the meantime, though, it seems like it would work (and p=
robably be faster anyway) if you spelled the default value like '0'::uint1 =
rather than 0::uint1, as the former isn't depending on any run-time cast.  =
So try altering the domain like that and then doing the upgrade.

			regards, tom lane