From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: jesper.pedersen@redhat.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15587: Partitions with ALTER TABLE ADD CONSTRAINT
Date: Thu, 10 Jan 2019 14:11:23 -0300
Message-ID: <201901101711.ngskkvwyqgr6@alvherre.pgsql> (raw)
In-Reply-To: <15587-562060223190c97a@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@2ndquadrant.com, jesper.pedersen@redhat.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15587: Partitions with ALTER TABLE ADD CONSTRAINT
In-Reply-To: <201901101711.ngskkvwyqgr6@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox