Re: [PG19] pg_upgrade --check passes, then the upgrade fails

On Fri, Oct 23, 2026 at 9:05 PM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> i hackers,
>
> From 18 to 19, pg_upgrade --check passes for both of these, then the
> upgrade fails:
>
> alter role app set standard_conforming_strings = off;
> ERROR: non-standard string literals are not supported
>
> create cast (uuid as bytea) with function uuid_send(uuid);
> ERROR: cast from type uuid to type bytea already exists

Thank you for the report. I've confirmed that pg_upgrade failed if
there is a role with standard_conforming_string = off or a cast for
uuid to bytea while pg_upgrade --check passes.

>
> With the patches, --check reports these before the upgrade starts.
> 0001 lists roles, databases and functions that store a setting the new
> cluster rejects.
>
> 0002 lists user casts that are now built in. Both ask the new cluster,
> so the next removed setting or new cast is caught too.

Both patches look like they are trying to resolve these issues in a
generic way, but I'm concerned that they are adding more complexity,
which is not great especially for v19. Also, the 0001 patch seems to
try resolving a wider range of issues than the actual issue that we
currently have. check_for_stored_settings() checks if there is a GUC
parameter that doesn't exist on the new version set to roles,
databases and functions, but it doesn't deal with the
standard_conforming_strings case where a GUC parameter still exists
but is no longer usable. So it ended up hard-coding it:

+ /* Per-role and per-database settings are cluster-wide. */
+ conn = connectToServer(cluster, "template1");
+ res = executeQueryOrDie(conn,
+ "SELECT d.datname, r.rolname, "
+ " pg_catalog.split_part(s.c, '=', 1) AS name, "
+ " pg_catalog.lower(s.c) ~ '" SCS_OFF_PATTERN "' AS scs_off "
+ "FROM (SELECT setdatabase, setrole, "
+ " pg_catalog.unnest(setconfig) AS c "
+ " FROM pg_catalog.pg_db_role_setting "
+ " WHERE setdatabase <> 0 OR setrole <> 0) s "
+ " LEFT JOIN pg_catalog.pg_database d "
+ " ON d.oid = s.setdatabase "
+ " LEFT JOIN pg_catalog.pg_roles r "
+ " ON r.oid = s.setrole "
+ "WHERE pg_catalog.strpos(pg_catalog.split_part(s.c, '=', 1), '.') = 0");

I think that a better fix for v19 would be to check these particular
cases (i.e., standard_conforming_strings = off, escape_string_warning,
and user-defined uuid<->bytea casts) only if the old cluster is 18 or
older, like check_for_not_null_inheritance() and
check_for_gist_inet_ops() do, and we can seek a more generic approach
for v20+.

> I think this is a 19 open item.

Agreed.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com