SOLVED: Re: pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

On Fri, Jan 08, 2016 at 04:26:25PM -0500, Tom Lane wrote:

> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> > On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote:
> >> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
> >> fix this particular problem, but after studying the code I realized that
> >> there's a whole bunch of related problems; for instance I believe
> >> pg_upgrade would lose domain constraints on a domain type that's in an
> >> extension installed into pg_catalog :-(.
>
> > Does this warrant adding a few words to the documentation
> > warning against installing extensions into pg_catalog. ?
>
> No, it's just a bug. Although apparently not many people do that, or
> we'd have heard complaints before.

For the record, apart from the aforementioned bug, I can
confirm that pg_upgrade will work fine when pg_trgm is
relocated to another schema (I chose "pgtrgm").

Caveats:

1)
One can't use "pg_trgm" as the schema name - PG will tell us
that the pg_ prefix is reserved for system schemata.

2)
One can't (easily ?) use

alter extension ... set schema ...

to relocate pg_trgm from pg_catalog to some other schema
because PG will inform us that pg_catalog is a system catalog:

gnumed_v21=# alter extension pg_trgm set schema pg_catalog;
ALTER EXTENSION
gnumed_v21=# alter extension pg_trgm set schema pgtrgm;
ERROR: cannot remove dependency on schema pg_catalog because it is a system object
gnumed_v21=#

Relocating from pg_catalog requires a

drop extension ... cascade
create extension ... with schema

cycle, followed by recreating GIN indexes as needed (in my case).

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346