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 1koDcA-0006pK-9P for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Dec 2020 22:43:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1koDc8-0000dm-0t for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Dec 2020 22:43:44 +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 1koDDc-0007Aa-Lb for pgsql-bugs@lists.postgresql.org; Sat, 12 Dec 2020 22:18:25 +0000 Received: from forward4-smtp.messagingengine.com ([66.111.4.238]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1koDDY-0005pT-EU for pgsql-bugs@lists.postgresql.org; Sat, 12 Dec 2020 22:18:23 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailforward.nyi.internal (Postfix) with ESMTP id 276581941DA7; Sat, 12 Dec 2020 17:18:18 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Sat, 12 Dec 2020 17:18:18 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; bh=hS54RoSG+gZgYnJfO1s+Vkt1RapVPIsB9jf3ICKDS Y4=; b=ZY9Q/zOL0sIXHnF4B4k4iGSMchnThfuGtKQhqela5PnsbgoyGCXMlsOr6 8OZsLlVjRns1rr78L1pY+KTOA1amyd86ulFg/zMpa2Qr+FF3tKtkZiUGLx+zhrfE TjQDQSbXes5L+c3dPaalo6szIT7N054bGfA8n7LWj9OXsxoBPAgK7mHv78DRtKl2 QQfMZWQ0KobQuIshWXIfvcPCLXuz3SAlAwATF9jZetF6JHXzLIrEUdEfsXLM+W/f cvsVK+l56KeXz+OxYRexDiSD8IJ3CBk7fKrTd+RjKSf65RuSU7SnKWss3OmkHaMz pAp/zj0wnM/CrXDrvGkuosvkxLfrw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedujedrudekgedgudeitdcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepuffvfhfhohfkffgfgggjtgfgse htjeertddtfeejnecuhfhrohhmpefrvghtvghrucfgihhsvghnthhrrghuthcuoehpvght vghrrdgvihhsvghnthhrrghuthesvghnthgvrhhprhhishgvuggsrdgtohhmqeenucggtf frrghtthgvrhhnpeehheeijefgvefhvdehleekueevhfehhedvheetgffhteefgeeggfev jeejfffgvdenucfkphepkeejrddujeejrdejvddrudegvdenucevlhhushhtvghrufhiii gvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehpvghtvghrrdgvihhsvghnthhrrghu thesvghnthgvrhhprhhishgvuggsrdgtohhm X-ME-Proxy: Received: from april.pezone.net (p57b1488e.dip0.t-ipconnect.de [87.177.72.142]) by mail.messagingengine.com (Postfix) with ESMTPA id 6B3C7240064; Sat, 12 Dec 2020 17:18:16 -0500 (EST) Subject: Re: BUG #16767: Silent dropping of CONSTRAINT... UNIQUE To: Tom Lane , boekewurm+postgres@gmail.com References: <16767-1714a2056ca516d0@postgresql.org> <3254991.1607446081@sss.pgh.pa.us> Cc: pgsql-bugs@lists.postgresql.org From: Peter Eisentraut Organization: EnterpriseDB Message-ID: <03247a85-e663-159b-c825-4334aa479fd4@enterprisedb.com> Date: Sat, 12 Dec 2020 23:18:15 +0100 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <3254991.1607446081@sss.pgh.pa.us> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2020-12-08 17:48, Tom Lane wrote: > PG Bug reporting form writes: >> I've just noticed that equivalent unique constraints that are specified in >> the same statement only generate one constraint; > > Yeah, that's intentional. Per the source code comments: > > * Scan the index list and remove any redundant index specifications. This > * can happen if, for instance, the user writes UNIQUE PRIMARY KEY. A > * strict reading of SQL would suggest raising an error instead, but that > * strikes me as too anal-retentive. - tgl 2001-02-14 It's nonetheless inconsistent that you can create redundant unique constraints via ALTER TABLE, but doing it in CREATE TABLE results in different behavior. This all seems a bit dubious to me. We should either allow it or prohibit it, not silently do something else in some cases.