From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexey Bashtanov <bashtanov@imap.cc>
Cc: pgsql-bugs@postgresql.org
Subject: Re: pg_dump losing index column collations for unique and primary keys
Date: Tue, 03 Dec 2019 14:52:13 -0500
Message-ID: <17844.1575402733@sss.pgh.pa.us> (raw)
In-Reply-To: <d1b13999-3808-8e65-6829-f22bb926206b@imap.cc>
References: <b05ce36a-cefb-ca5e-b386-a400535b1c0b@imap.cc>
<13713.1575392277@sss.pgh.pa.us>
<6484.1575397478@sss.pgh.pa.us>
<d1b13999-3808-8e65-6829-f22bb926206b@imap.cc>
Alexey Bashtanov <bashtanov@imap.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.1558909428@sss.pgh.pa.us
If you'd like to help move things along by reviewing that patch,
it'd be great.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, bashtanov@imap.cc
Subject: Re: pg_dump losing index column collations for unique and primary keys
In-Reply-To: <17844.1575402733@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox