On Fri, Oct 23, 2026 at 9:05 PM shihao zhong <[email protected]> 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
