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@[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@[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