From: Tom Lane <tgl@sss.pgh.pa.us>
To: Erik Wienhold <ewie@ewie.name>
Cc: Alexey Murz Korepov <murznn@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Ways to change a database collation with removing duplicates
Date: Sat, 11 Mar 2023 15:53:23 -0500
Message-ID: <1253567.1678568003@sss.pgh.pa.us> (raw)
In-Reply-To: <1378811615.328761.1678562412852@office.mailbox.org>
References: <CAL5pyKvXL69bRrONgkutz=xeCUQz6QO7GN_JndqBD0Mq5OJJ-Q@mail.gmail.com>
<1378811615.328761.1678562412852@office.mailbox.org>
Erik Wienhold <ewie@ewie.name> writes:
> On 11/03/2023 19:23 CET Alexey Murz Korepov <murznn@gmail.com> wrote:
>> Could anyone suggest to me the ways to change a database collation with
>> removing all the duplicates, caused by this change?
> Collations can only affect uniqueness if they are nondeterministic or if you
> have functional indexes, e.g. using lower(text) for a case-insensitive unique
> index. Otherwise the collations only affect text ordering.
Yeah. I suspect that what actually happened here was a previous change in
the host system's sort ordering (cf [1]), leading to text indexes becoming
functionally corrupt and unable to enforce uniqueness correctly, after
which you accumulated some unintentional duplicates. If you try
reindex'ing on the source database you'll probably find that it fails with
the same errors. I don't know of any automatic tools for fixing up such
duplications, and wouldn't trust one hugely anyway --- you'll probably
need manual curation of the fixes.
regards, tom lane
[1] https://wiki.postgresql.org/wiki/Locale_data_changes
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, ewie@ewie.name, murznn@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Ways to change a database collation with removing duplicates
In-Reply-To: <1253567.1678568003@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