Re: BUG #15685: pg_upgrade fails to migrate DEFAULT values that use custom TYPEs or FUNCTIONs

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> we've recently migrated from PostgreSQL 9.5 to 11.2 and after pg_upgrade a
> couple of tables were in a "broken" state afterwards where they were lacking
> DEFAULT values that relied on custom TYPEs or FUNCTIONs so that after the
> upgrade, we needed to run migrations such as

> ALTER TABLE IF EXISTS table1
> ALTER COLUMN column1 SET DEFAULT unix_timestamp()
> ALTER TABLE IF EXISTS table2
> ALTER COLUMN column2 SET DEFAULT 'open'::recruitment_state

It's hard to say anything definitive without a lot more detail, but
my guess is that this was triggered by the recent security-related
changes to make pg_dump scripts run with restrictive search_path
settings. Probably what happened is that the custom objects you
were relying on contain non-schema-qualified references that fail
with a restrictive search_path, so that they didn't restore into
the new database. It's unclear though why that wouldn't have led
to pg_upgrade failing altogether.

regards, tom lane