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

The following bug has been logged on the website:

Bug reference: 15685
Logged by: Justus Graf
Email address: justusgraf(at)gmx(dot)de
PostgreSQL version: 11.2
Operating system: Debian GNU/Linux 9.8 (stretch)
Description:

Hey there,

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

We did experience something similar when using pg_restore in the past, not
sure if that helps or if you could give us any hints how this can be
avoided?

Best,

Justus

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