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 1jOpjl-0002st-0B for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Apr 2020 21:38:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jOpjj-0001uf-H0 for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Apr 2020 21:38:23 +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 1jOpjj-0001uY-Am for pgsql-bugs@lists.postgresql.org; Wed, 15 Apr 2020 21:38:23 +0000 Received: from mail-wm1-x343.google.com ([2a00:1450:4864:20::343]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jOpjg-0007DL-GK for pgsql-bugs@lists.postgresql.org; Wed, 15 Apr 2020 21:38:21 +0000 Received: by mail-wm1-x343.google.com with SMTP id y24so1773851wma.4 for ; Wed, 15 Apr 2020 14:38:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=bKYq3Nr/COXKdt1/2M2fLtYCvd/3sylibaeJDpYrDng=; b=FDeFakac8i/6hSSveAT+8IhIwMrrpWyxMdqCALhYeqlZgxGKAow+tu5ZfvBbpbINa7 j7ozSAP9K909YHgeADLc0wSSEOrLhkmZ/NlleTUDJ1tDl8lmZZishevTxnTU3MCdb/+N m+nmhF1iyUuRzrLoU2K8J+oBfAg3RoH+vTjjHzpnp0QnUeqeIW/RQd539dN+NPfpsFFB 2MlL3Q5Mf2GcGtWFc3FU1aMuUQmzB1j28mH+n73UHbWBjm5qGU79Afdy84xUXAwAOb+6 RTfusrwi3RlgvfZSYy+Xw57BFNKWg3uZ2T4gTm9uujWEtfSsrQeW8CoxlXi1mfNn6Yo/ XAqw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=bKYq3Nr/COXKdt1/2M2fLtYCvd/3sylibaeJDpYrDng=; b=duziTHIVpdtqaAoTlkQ1l+LxIaC5TtJSdmHxA2SCg7e8up/6UxBCvIuKfdmg91JOoW dIDwAgD2U1c6JgIzAPtCEUehPpgchdJUvCezfOiW/v3mUS3YSBjKoae7Mbg0kpSOisrj RCU77FdPPgcfFP3okmRSe6Pyiup4DV3zVeMGb8pOuakGQpycBhowm/VXIkEKPQvuYtwT nUqw8LbIB5L+gg9O30mKBpxhp9MXZl5+f62RVSjabVSkR/UKyMQ56K40Hp6J7GzjGCq0 ylSE1i9dRYuvK1c6HnS7JtDzfr5yX9YfCSpQWYkjGgqdUxoTIDrkughGZE64mzl6ivsR Pr3Q== X-Gm-Message-State: AGi0PubyeB/yUpSVr/7cLCe1RmmPGaJdPTA/J//ziBZPx4VHpEvhp4KQ BlbbZS5HNOBMyZAA9TGCt9XGig== X-Google-Smtp-Source: APiQypKDN7Yiw7+qB17iRP8/dqTVNkjAqh+IxFuvx60FuJ88AZBMcypQ96Xa/iurKFemZgZeAwhLzg== X-Received: by 2002:a7b:c4c9:: with SMTP id g9mr1251077wmk.171.1586986698713; Wed, 15 Apr 2020 14:38:18 -0700 (PDT) Received: from localhost (ip-86-49-253-141.net.upcbroadband.cz. [86.49.253.141]) by smtp.gmail.com with ESMTPSA id k14sm25487476wrp.53.2020.04.15.14.38.17 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 15 Apr 2020 14:38:17 -0700 (PDT) Date: Wed, 15 Apr 2020 23:38:16 +0200 From: Tomas Vondra To: KrishnaKanth BN Cc: krishnakanth , pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16352: Data corruption in few tables Message-ID: <20200415213816.qupnu726pct6mryn@development> References: <16352-8a22275b605f48b8@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Wed, Apr 15, 2020 at 10:39:19PM +0530, KrishnaKanth BN wrote: >Hi All, > >Can you please help us on this issue? > Well, this very much sounds like a case of data corruption caused by storage - either at OS or hardware level. You'll have to dig in dmesg and other system logs I guess. >On Wed 8 Apr, 2020, 21:30 PG Bug reporting form, >wrote: > >> The following bug has been logged on the website: >> >> Bug reference: 16352 >> Logged by: KrishnaKanth BN >> Email address: Krishnakanth_bn@infosys.com >> PostgreSQL version: 9.6.13 >> Operating system: Docker image postgres:9.6.13 >> Description: >> >> Our Application is using Postgres Version (Image- postgres:9.6.13) and we >> are running it as Docker Container with Volume mounted to a NFS storage. We >> came across a peculiar issue. >> Well, I wouldn't be surprised if this was related to NFS. >> We had enabled unique key constraint on a particular table, it was violated >> and had duplicate data. When this table was queried in the application, it >> was resulting in chunk errors. This had major outage in the application. >> I very much doubt creating a unique constraint would cause this, even if the creation failed because of duplicate values. It might have made the issue visible, but that's a different thing. >> ERROR: missing chunk number 0 for toast value 241012 in pg_toast_2619 >> STATEMENT: SELECT distinct role_name FROM tapplication_roles INNER JOIN >> troles ON tapplication_roles.role_id = troles.role_id where user_id in >> ('user1','value1') >> This more of less confirms it has nothing to do with the constaint, because "missing chunk" refers to TOAST table (we don't have chunks anywhere else) and that's only for tables, not for indexes. >> We followed several posts and somehow managed to remove the duplicate rows >> and reindexed and vacuumed the table. We also had to manually remove >> duplicate entries in pg_statistic table and reindex and vaccum it. >> >> There are couple of other tables in which few rows are corrupted. It is >> because of a corrupt value in one of the columns. >> Well, it'd be useful to actually show us what you did etc. >> We did not see any error in the container logs to isolate this issue when >> it >> first occurred. >> >> Can you please throw some light what might have caused the corruption and >> how can we prevent it? >> Most likely some sort of storage issue - not necessarily at hardware level, but it might be at OS level. Not sure if docker, or maybe it's a networking / NFS issue. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services