Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fhhPw-0005ON-Al for pgsql-admin@arkaria.postgresql.org; Mon, 23 Jul 2018 20:26:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fhhPu-0005hj-Kn for pgsql-admin@arkaria.postgresql.org; Mon, 23 Jul 2018 20:26:50 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fhhPu-0005hc-9Y for pgsql-admin@lists.postgresql.org; Mon, 23 Jul 2018 20:26:50 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fhhPr-0006WV-Rb for pgsql-admin@lists.postgresql.org; Mon, 23 Jul 2018 20:26:49 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id E5C92218EB; Mon, 23 Jul 2018 16:26:46 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Mon, 23 Jul 2018 16:26:46 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= cc:content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; bh=+SVExmWFvZZqfJwrZ4NR4vaxxo5txTknw9vazpdhMA0=; b=B+t5jovE 8YkhyZ70JftLzI/pEKzS60NCAZ0YLeQk3C6Ylh26uhw++RUi2S2DDgdZeZgqUZ06 if9X7S4gN0vaHpGL/R1SqyMOv5UUA9qqonRUJjMpVQFviOG5Dq/qMLOGiE/LGU2x R/Vi5xtK/7B4Tdsgt6Xh9OB1TD6RH5Bg66gyBsJbu1j11MD7IBdXj/JqeiwqmD0+ G/oRf5cW5fF3EMJzftkymeG6bxqp5QMiFEteEHlPSAMux8JnRtWMFUQX40q4mzCo LiE37ezOT023MK8QMhoD3xi4SvYEDrWpyh3RbIF+IsWkzCjqpLLuqDPoRPqnWAEo cqZM1bndzwE9ow== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; bh=+SVExmWFvZZqfJwrZ4NR4vaxxo5tx Tknw9vazpdhMA0=; b=mmXxwwsfjy2riOlLMSCYaFzIX7jFRo6apSUIu9dxj6h50 ObsOZIKPT3GHRoZhQiNwGeL5WJBq7UAux9IoXzc46lCbXaKqUbFAaOYad0Ry90wI J3mUNTo5BMjgsRaxcXO/CyUP/9SDrC/PDaZ0clZocDMlDW2RnpT0bl3kxGPG8WYw UIYknvmR7TtfD9B3y7JLAL6gVC6Sbw0/JyFjjzTej5LFrNPgcOU+GE91L361VFBb /LS8d6jO/wOFOEW2pTX7EP1XG08QkxxAneIlwrdHUAFmAsNADXF9CCV7xL2he/vh +hrJ4ZvI7hho0bNHdzwTGAyEYW2hzuYT0Qopq4hiw== X-ME-Proxy: X-ME-Sender: Received: from intern.anarazel.de (unknown [172.58.94.173]) by mail.messagingengine.com (Postfix) with ESMTPA id 5DE4710273; Mon, 23 Jul 2018 16:26:46 -0400 (EDT) Date: Mon, 23 Jul 2018 13:26:45 -0700 From: Andres Freund To: Mariel Cherkassky Cc: pgsql-admin@lists.postgresql.org Subject: Re: pg_authid has duplicated rows Message-ID: <20180723202645.dv47gmkbqk5kbrfr@alap3.anarazel.de> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On 2018-07-23 21:03:32 +0300, Mariel Cherkassky wrote: > Today I faced a situation where I saw a postgresql 9.2 database that had > duplicated rows in pg_autid. I saw 6 roles while 3 of them were duplicated > (3 uniques rolenames in total). I checked and the oid of the duplicates > were the same oid as the originals. The solution was running vacuum on the > table but I wanted to understand how this situation was created ? Why even > with unique index on the rolname I saw duplicated values for rolname ? That suggests some form of corruption. Which version of 9.2 did this occur on? Did you have any OS level crashes? Ran with fsync=off? Please note that 9.2 is not supported anymore. Greetings, Andres Freund