Date: Wed, 9 Jan 2019 00:51:09 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
	Teodor Sigaev <teodor@sigaev.ru>
Subject: unique, partitioned index fails to distinguish index key from
 INCLUDEd columns
Message-ID: <20190109065109.GA4285@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

eb7ed3f3063401496e4aa4bd68fa33f0be31a72f Allow UNIQUE indexes on partitioned tables
8224de4f42ccf98e08db07b43d52fed72f962ebb Indexes with INCLUDE columns and their support in B-tree

postgres=# CREATE TABLE t(i int,j int) PARTITION BY LIST (i);
postgres=# CREATE TABLE t1 PARTITION OF t FOR VALUES IN (1);
postgres=# CREATE TABLE t2 PARTITION OF t FOR VALUES IN (2);

-- Correctly errors
postgres=# CREATE UNIQUE INDEX ON t(j);
ERROR:  insufficient columns in UNIQUE constraint definition
DETAIL:  UNIQUE constraint on table "t" lacks column "i" which is part of the partition key.

-- Fails to error
postgres=# CREATE UNIQUE INDEX ON t(j) INCLUDE(i);

-- Fail to enforce uniqueness across partitions due to failure to enforce inclusion of partition key in index KEY
postgres=# INSERT INTO t VALUES(1,1);
postgres=# INSERT INTO t VALUES(2,1); 

postgres=# SELECT * FROM t;
 i | j 
---+---
 1 | 1
 2 | 1
(2 rows)

I found this thread appears to have been close to discovering the issue ~9
months ago.
https://www.postgresql.org/message-id/flat/CAJGNTeO%3DBguEyG8wxMpU_Vgvg3nGGzy71zUQ0RpzEn_mb0bSWA%40mail.gmail.com

Justin