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

From: Alexey Bashtanov <bashtanov(at)imap(dot)cc>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_dump losing index column collations for unique and primary keys
Date: 2019-12-03 19:30:49
Message-ID: d1b13999-3808-8e65-6829-f22bb926206b@imap.cc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-12-03 19:52:13 Re: pg_dump losing index column collations for unique and primary keys
Previous Message Tom Lane 2019-12-03 18:24:38 Re: pg_dump losing index column collations for unique and primary keys