Some more thoughts:
1. If you create an index on an expression that includes a COLLATE or
a partial index that has one in the WHERE clause, you get bogus
warnings:
postgres=# create table t (v text);
CREATE TABLE
postgres=# create index on t(v) where v > 'hello' collate "en_NZ";
WARNING: index "t_v_idx3" depends on collation "en_NZ" version "",
but the current version is "2.28"
DETAIL: The index may be corrupted due to changes in sort order.
HINT: REINDEX to avoid the risk of corruption.
CREATE INDEX
postgres=# create index on t((case when v < 'x' collate "en_NZ" then
'foo' else 'bar' end));
WARNING: index "t_case_idx" depends on collation "en_NZ" version "",
but the current version is "2.28"
DETAIL: The index may be corrupted due to changes in sort order.
HINT: REINDEX to avoid the risk of corruption.
CREATE INDEX
That's because the 0003 patch only calls recordDependencyOnVersion()
for simple attribute references. When
recordDependencyOnSingleRelExpr() is called by index_create() to
analyse ii_Expressions and ii_Predicate, it's going to have to be
smart enough to detect collation references and record the versions.
There is also some more code that ignores pinned collations hiding in
there.
This leads to the difficult question of how you recognise a real
dependency on a collation's version in an expression. I have some
vague ideas but haven't seriously looked into it yet. (The same
question comes up for check constraint -> collation dependencies.)
2. If you create a composite type with a text attribute (with or
without an explicit collation), and then create an index on a column
of that type, we don't record the dependency.
postgres=# create type my_type as (x text collate "en_NZ");
CREATE TYPE
postgres=# create table t (v my_type);
CREATE TABLE
postgres=# create index on t(v);
CREATE INDEX
postgres=# select * from pg_depend where refobjversion != '';
classid | objid | objsubid | refclassid | refobjid | refobjsubid |
refobjversion | deptype
---------+-------+----------+------------+----------+-------------+---------------+---------
(0 rows)
I think create_index() will need to perform recursive analysis on
composite types to look for text attributes, when they appear as
simple attributes, and then add direct dependencies index -> collation
to capture the versions. Then we might need to do the same for
composite types hiding inside ii_Expressions and ii_Predicate (once we
figure out what that really means).
3. Test t/002_pg_dump.pl in src/bin/pg_upgrade fails.
4. In the warning message we should show get_collation_name() instead
of the OID.
