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

Alexey Bashtanov <bashtanov(at)imap(dot)cc> writes:
>> 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.

Well, I disagree that that's a high-priority use case, but it seems to me
that you can still do it. You just can't call the index the pkey until
the column collation agrees. So roughly it'd be

* CREATE UNIQUE INDEX ... (col COLLATE new-collation)
* Drop old pkey (the new index is still enforcing uniqueness)
* ALTER TABLE ... ALTER COLUMN ... COLLATE new-collation
* ALTER TABLE ADD PRIMARY KEY USING INDEX

These seem to me to be pretty much exactly the same steps you'd need
today, although maybe the current code is more forgiving about
their ordering.

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

That restriction has been there since day one, and we have had a grand
total of zero complaints about it.

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

Yeah, see
https://www.postgresql.org/message-id/flat/10365(dot)1558909428(at)sss(dot)pgh(dot)pa(dot)us
If you'd like to help move things along by reviewing that patch,
it'd be great.

regards, tom lane