Re: 16: Collation versioning and dependency helpers

On Sun, 2022-10-30 at 19:10 +1300, Thomas Munro wrote:
> FWIW we did this (plus a lot more) in the per-index version tracking
> feature reverted from 14.

Thank you. I will catch up on that patch/thread.

> >   0002: Enable pg_collation_actual_version() to work on the default
>
> Makes sense.
>
> >   0003: Fix ALTER COLLATION "default" REFRESH VERSION, which
>
> Makes sense.

Committed these two small changes.

> >   0004: Add system views:
> >     pg_collation_versions: quickly see the current (from the
> > catalog)
> > and actual (from the provider) versions of each collation
> >     pg_collation_dependencies: map of objects to the collations
> > they
> > depend on
> >
> > Along with these patches, you can use some tricks to verify data,
> > such
> > as /contrib/amcheck; or fix the data with things like:
> >
> >   * REINDEX
> >   * VACUUM FULL/TRUNCATE/CLUSTER
> >   * REFRESH MATERIALIZED VIEW
> >
> > And then refresh the collation version when you're confident that
> > your
> > data is valid.
>
> Here you run into an argument that we had many times in that cycle:
> what's the point of views that suffer both false positives and false
> negatives?

The pg_collation_versions view is just a convenience, useful because
the default collation isn't represented normally in pg_collation so it
needs to be special-cased.

I could see how it would be tricky to precisely track the dependencies
through composite types (that is, create the proper pg_depend records),
but to just provide a view of the affected-by relationship seems more
doable. I'll review the previous discussion and see what I come up
with.

Of course, the view will just show an "affected by" relationship, it
won't show which objects are actually in violation of the current
collation version. But it at least gives the administrator a starting
place.

Regards,
Jeff Davis