Date: Thu, 10 Jan 2019 14:11:23 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: jesper.pedersen@redhat.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15587: Partitions with ALTER TABLE ADD CONSTRAINT
Message-ID: <201901101711.ngskkvwyqgr6@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <15587-562060223190c97a@postgresql.org>
User-Agent: NeoMutt/20180716-1013-2c1e88
Precedence: bulk

On 2019-Jan-10, PG Bug reporting form wrote:

> The following works
> 
> CREATE TABLE t1 (i1 INT NOT NULL, i2 INT NOT NULL) PARTITION BY HASH (i1);
> 
> \o /dev/null
> SELECT 'CREATE TABLE t1_p' || x::text || ' PARTITION OF t1
> FOR VALUES WITH (MODULUS 64, REMAINDER ' || x::text || ');'
> from generate_series(0,63) x;
> \gexec
> \o
> 
> ALTER TABLE ONLY t1 ADD CONSTRAINT uniq_t1_i1_i2 UNIQUE (i1, i2);
> 
> which gives
> 
> test=# \d+ t1
>                               Partitioned table "public.t1"
>  Column |  Type   | Collation | Nullable | Default | Storage | Stats target
> | Description 
> --------+---------+-----------+----------+---------+---------+--------------+-------------
>  i1     | integer |           | not null |         | plain   |             
> | 
>  i2     | integer |           | not null |         | plain   |             
> | 
> Partition key: HASH (i1)
> Indexes:
>     "uniq_t1_i1_i2" UNIQUE CONSTRAINT, btree (i1, i2) INVALID
> 
> 
> Removing ONLY from the ALTER command makes the index correct.

I'm not clear what problem you're reporting.  If you use ONLY, then the
command doesn't cascade to create the index on partitions, and the index
is marked invalid.  If you add the constraint to each partition and
ALTER INDEX ATTACH PARTITION, the index on t1 should become valid when
every partition of the table has its index.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services