Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gnQnr-00033E-1k for pgsql-admin@arkaria.postgresql.org; Sat, 26 Jan 2019 16:27:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gnQno-0003Qr-Af for pgsql-admin@arkaria.postgresql.org; Sat, 26 Jan 2019 16:27:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gnQno-0003Qj-13 for pgsql-admin@lists.postgresql.org; Sat, 26 Jan 2019 16:27:28 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gnQnl-0001SK-QP for pgsql-admin@lists.postgresql.org; Sat, 26 Jan 2019 16:27:27 +0000 Received: by mail-ot1-x329.google.com with SMTP id t5so11293395otk.1 for ; Sat, 26 Jan 2019 08:27:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-transfer-encoding:content-language; bh=vt3G5mkvSBAdqrTZilvM9Fq7k3mzMOO3ou8T7qGi+k0=; b=rxQ+sphVK/LXIMjfGSz05jB4ZqL48WI1Y98BdkFpuUEovbKnIYVp20dE+TKsDXAMOX eH0G79Y9TwWjg6sP+6hksC0EhpKCfI6DRz+FnR72mocTebvZSrGplnFVRWEA4Iv1nFn/ +nOC5UT/FRBNIdb85e3S6PfOqbdfIRn+1YcqHUfYANcWC+KG82/B9UjVcrnZ23e2tDo8 TFAKjpVMErBy8GTLe8q+vI5VhT8uQbLcjG3l9Uf0Iz/Hvae6ZQojXW4pHDbNoIqzlzIJ eLK72Vq1VPA4DtcM1nYRMTfHaWZZLy78ez585HRKVmZ3sUgsl8ZFpmqIuU9H4WxCqeqa UMhg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-transfer-encoding :content-language; bh=vt3G5mkvSBAdqrTZilvM9Fq7k3mzMOO3ou8T7qGi+k0=; b=FObU8Kkjf8NRszs1ej6nPudMCtsQEMaGCTnkGSuT0o/u0F1/y47eaZ02tY/8GYpRUL 1HVsECJKUZbR1k2ugDunDdT6AaVIfN4/cl2k49hK8c4zbpTpiPug6nQVP58x3w09GPVS IE4VJ0tJPNT+M7AMOy5gaM9Y3d2zfYkFS93gvuUzUfSd0fOP5a/KgSQiVlsatznUA7WR 36BdD0qe9SyBehwlGGRXuuVaEv26eNLw9yFM0Xp7bnHe3hYqzm2Iu3g5o9jfn+thdrMH o8is6oL3TRi01fohjdoPVdCcKnaaeQC6325qgE3HL8DAvcDgJZhMJZGXJ4IfJO1EeV9J Qc2A== X-Gm-Message-State: AJcUuke1XCN2KFh0foNKBbAd9uH/xfjJ1QN4d3s+uaCGcN952+5NeLiT 0sSIytV5qK7QcMGp8G7eIAUz3tGN X-Google-Smtp-Source: ALg8bN4jp0jReyr4PcibeLq7Vfomytgj76h8L/pDM90Hi457oVqOCbgHWUoD4B0GcuByFmxyFX07vw== X-Received: by 2002:a9d:60cf:: with SMTP id b15mr10932922otk.144.1548520042299; Sat, 26 Jan 2019 08:27:22 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id i5sm2560237oia.46.2019.01.26.08.27.20 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 26 Jan 2019 08:27:21 -0800 (PST) Subject: Re: Index scan does not return all the data To: pgsql-admin@lists.postgresql.org References: <4b961a85-94cf-39c5-2432-fc3abd0be695@quadratum-braccas.com> From: Ron Message-ID: <8be4b59a-482f-0eff-b271-223cd8dba19a@gmail.com> Date: Sat, 26 Jan 2019 10:27:20 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: <4b961a85-94cf-39c5-2432-fc3abd0be695@quadratum-braccas.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 1/26/19 9:33 AM, S. Bob wrote: > Hi all; > > > we have a client that has run into an issue, they have a PK on an id > column and another unique index on the id and a timestamp. when we run a > select it selects one of several indexes, in some cases a non unique index > on a secondary ID, however when we do a pg_dump, or a CREATE TABLE x AS > select * from this table then we get many many duplicates that violate the > PK and the unique index. > > Thoughts? Sounds like a corrupt index.  "REINDEX TABLE foo" and -- if not already -- update to the latest release of your version. -- Angular momentum makes the world go 'round.