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