Re: pg_dump losing index column collations for unique and primary keys

Hi Tom,

>> In short, I'd say the bug here is not pg_dump's fault at all,
>> but failure to insist on collation match in ADD PRIMARY KEY
>> USING INDEX.
While being entirely reasonable especially with the non-deterministic
collations,
this breaks down an important facility of changing a column collation
without
rewriting it even if it is a part of a unique constraint: first change
the PK then the
column itself.

> Concretely, I think we should do the attached.

While being entirely reasonable especially with the non-deterministic
collations,
this breaks down an important facility of changing a column collation
without
rewriting it even if it is a part of a unique constraint: first change
the PK then the
column itself.

Disallowing changing the direction (ASC/DESC) also looks cruel to me.

BTW with playing with this stuff I came across another issue,
which seems unrelated to collations:

2019-12-03 19:08:26 alexey(at)[local]/alexey=# \d g
                 Table "public.g"
┌────────┬──────┬────────────┬──────────┬─────────┐
│ Column │ Type │ Collation  │ Nullable │ Default │
├────────┼──────┼────────────┼──────────┼─────────┤
│ a      │ text │ en_US.utf8 │ not null │         │
└────────┴──────┴────────────┴──────────┴─────────┘
Indexes:
    "g_pkey" PRIMARY KEY, btree (a)
    "g_a_idx" UNIQUE, btree (a)

2019-12-03 19:08:27 alexey(at)[local]/alexey=# begin; alter table g drop
constraint g_pkey, add primary key using index g_a_idx, alter column a
type text;
BEGIN
Time: 0.421 ms
ERROR:  could not open relation with OID 16417
Time: 9.990 ms

Is it something known?

From user's perspective I think an ideal solution would be to fix the above
and to enforce the PK/UK and column collation to be the same only
by the end of ALTER TABLE command (and document this, as it isn't very
obvious).
I haven't yet looked in the code though to see how comfortable would
that be to implement it.

Best, Alex