Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kSx9E-0003Oi-Kg for pgsql-admin@arkaria.postgresql.org; Thu, 15 Oct 2020 06:54:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kSx9D-0008Nh-IK for pgsql-admin@arkaria.postgresql.org; Thu, 15 Oct 2020 06:53:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kSx9D-0008NL-3t for pgsql-admin@lists.postgresql.org; Thu, 15 Oct 2020 06:53:59 +0000 Received: from mail4.neteven.com ([5.135.111.119]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kSx98-00063h-3O for pgsql-admin@lists.postgresql.org; Thu, 15 Oct 2020 06:53:57 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mail4.neteven.com (Postfix) with ESMTP id DD0BE29BF6; Thu, 15 Oct 2020 08:53:50 +0200 (CEST) DKIM-Filter: OpenDKIM Filter v2.11.0 mail4.neteven.com DD0BE29BF6 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=neteven.com; s=dkim; t=1602744830; bh=vpjDFoeK9PAF6g+e2/52Ay/n+ZRM+SnaB4hXjfi/4H0=; h=Subject:From:To:Cc:References:Date:In-Reply-To:From; b=XFO8cAirqGXw4pcHo5knAxibB/4FLvM63gWkNDll8BtJL+MrXUvTS71eNsSDDnRdA 682/sqY3BiqhGjHneZF/V7P2B+Tyq/NnvTLiRzsElTpCbntmoTfAN2HCfJRCuZOHAL 011Y0eFAyqTKPYu0u/qeLPjy1j5KdvBuz4XolQ7I= X-Virus-Scanned: Debian amavisd-new at mail4.neteven.com Received: from mail4.neteven.com ([127.0.0.1]) by localhost (mail4.neteven.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Ex1FC8lQBug8; Thu, 15 Oct 2020 08:53:50 +0200 (CEST) Subject: Re: Weird behavior with unique constraint not respected, and random results on same queries From: Thomas SIMON To: Magnus Hagander Cc: pgsql-admin@lists.postgresql.org References: <01d4165c-201a-971f-b1e4-acea04872238@neteven.com> <50676167-ccd7-6187-93ce-7dd082de3dd7@neteven.com> Message-ID: <40baef4b-b604-95f4-b467-3c347e095a2b@neteven.com> Date: Thu, 15 Oct 2020 08:53:49 +0200 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101 Thunderbird/78.3.2 MIME-Version: 1.0 In-Reply-To: <50676167-ccd7-6187-93ce-7dd082de3dd7@neteven.com> Content-Type: multipart/alternative; boundary="------------A93249446714D09DEADFB722" Content-Language: fr List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A93249446714D09DEADFB722 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi Magnus, Just for the record, you got it right with locale data change, after index rebuild, we no longer have duplicates. thanks again, have a good day thomas Le 12/10/2020 à 19:47, Thomas SIMON a écrit : > > Hi Magnus, > > thank you for you quick and relevant answer, you saved my day ! > > Le 12/10/2020 à 17:53, Magnus Hagander a écrit : >> >> >> On Mon, Oct 12, 2020 at 5:43 PM Thomas SIMON > > 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? > Yes old master was debian 9 >> >> 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. > I haven't found this note before, ans it seems indeed to describe the > exact problem that we have... > I just upgraded old master to avoid these kind of problems. >> >> >> 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. > Exactly yes >> >> 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. > > Yes, we are working on a script which will delete all duplicate > values, and then we will use reindex with concurrently option on all > listed lindexs > > We'll do these actions tomorrow morning, and I'll keep you updated > about the result. > >> >> -- >>  Magnus Hagander >>  Me: https://www.hagander.net/ >>  Work: https://www.redpill-linpro.com/ -- Thomas SIMON Responsable Infrastructures Neteven --------------A93249446714D09DEADFB722 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Hi Magnus,

Just for the record, you got it right with locale data change, after index rebuild, we no longer have duplicates.

thanks again, have a good day

thomas

Le 12/10/2020 à 19:47, Thomas SIMON a écrit :

Hi Magnus,

thank you for you quick and relevant answer, you saved my day !

Le 12/10/2020 à 17:53, Magnus Hagander a écrit :


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?
Yes old master was debian 9

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.
I haven't found this note before, ans it seems indeed to describe the exact problem that we have...
I just upgraded old master to avoid these kind of problems.


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.
Exactly yes

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.

Yes, we are working on a script which will delete all duplicate values, and then we will use reindex with concurrently option on all listed lindexs

We'll do these actions tomorrow morning, and I'll keep you updated about the result.


-- 
-- 
Thomas SIMON
Responsable Infrastructures
Neteven
--------------A93249446714D09DEADFB722--