Re: Weird behavior with unique constraint not respected, and random results on same queries

Поиск
Список
Период
Сортировка
От Magnus Hagander
Тема Re: Weird behavior with unique constraint not respected, and random results on same queries
Дата
Msg-id CABUevEwX+YrJ42LQJJM6R40Fh+e4zOsGrkEceoCyHiuJf9oz=Q@mail.gmail.com
обсуждение исходный текст
Ответ на Weird behavior with unique constraint not respected, and random results on same queries  (Thomas SIMON <tsimon@neteven.com>)
Ответы Re: Weird behavior with unique constraint not respected, and random results on same queries
Список pgsql-admin
Дерево обсуждения


On Mon, Oct 12, 2020 at 5:43 PM Thomas SIMON <tsimon@neteven.com> wrote:
Hi all,

I encounter strange behavior since a few days, and the promote of a
recently installed server as master in my infrastructure (debian 10, was
added as slave a few days ago)
I use postgresql 12.4 on master an slave.

Is the previous master also Debian 10?

Specifically, when upgrading to Debian 10 you need to reindex all indexes on text fields (see https://wiki.postgresql.org/wiki/Locale_data_changes). This can in particular cause problems in replication, whereby your primary and standby nodes have to run the same version of Debian since you cannot do a separate reindex on the standby.

E.g. if your master is Debian 9, then you can expect some lookups to be incorrect on the standby, and after you do a failover you may see entries with invalid values in a unique index go in because of the lack of reindex.


Since the promote, I find on database some duplicated entries , despite
the fact that the constraint should not allow this.
It seems to only happens with entries having "special" characters
(understand " ", "-", "+", ...)

As you can see from the locale page above, the changes are specifically around special characters, so this is an indication that it might be this problem.

If this is the problem, the fix is a REINDEX and to make sure that all nodes in the replication cluster use the same version of the collections in the future. If the reindex fails because of existing duplicate entries, you will have to find a way to clean those up before reindexing. You may have to drop the indexes, then clean up, and then recreate them.

-- 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Thomas SIMON
Дата:
Сообщение: Weird behavior with unique constraint not respected, and random results on same queries
Следующее
От: Thomas SIMON
Дата:
Сообщение: Re: Weird behavior with unique constraint not respected, and random results on same queries