On 05.02.25 22:47, Jeff Davis wrote:
> (b) we should make reasonable attempts to mitigate potential
> problems.
>
> One idea for (b) resurfaced, which was to make a best-effort check at
> pg_upgrade time for affected indexes. The check would not be
> bulletproof, because we can't catch dependencies that are hidden inside
> SPI (e.g. a plpgsql function that calls LOWER()), but it would catch
> most potential problems.
>
> Patch attached. A few notes:
>
> * The dependency entries don't exist because LOWER(), etc., are
> system objects (pinned); so it queries the indexprs, indpreds,
> partexprs, and conbin.
> * The query is large and perhaps too clever, but it seems to work. I
> tried to add inline comments to the SQL, and pgindent had its own ideas
> about how to format them -- suggestions welcome.
> * We haven't actually done the Unicode update yet, so it will notice
> that the PG17 and PG18 Unicode versions are the same, and return early.
> Either apply on top of the Unicode update patch, or comment out the
> early return for testing.
> * It emits a warning rather than an error, so you need to specify
> pg_upgrade with "-r" to see the output file.
> * I didn't adapt the query to run on pre-17 versions, even though it
> could find some potential problem cases (like an index on NORMALIZE()).
> I can add that if someone thinks it's worthwhile.
This all looks quite reasonable to me. The code could obviously use a
bit closer inspection, but the way you've structured it looked quite
okay to me.
