Date: Mon, 19 Feb 2018 17:45:31 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
	Jaime Casanova <jaime.casanova@2ndquadrant.com>
Cc: Jesper Pedersen <jesper.pedersen@redhat.com>,
	Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
	Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: unique indexes on partitioned tables
Message-ID: <20180219204531.tsblwxwtz332pp7e@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20180219185430.bztdubmwhlceu6xs@alvherre.pgsql>
 <d3e5cfa4-15aa-48a4-7b9e-49825a35b75c@2ndquadrant.com>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk

I pushed this now, with fixes for the last few comments there were.

Peter Eisentraut wrote:

> I don't understand the variable name "third".  I don't see a "first" or
> "second" nearby.

Hah.  That was referring to variables "myself" and "referenced".  I
changed the variable name to "parentConstr".

> I find some of the columns in pg_constraint confusing.  For a primary
> key on a partitioned table, for the PK on the partition I get
> 
> conislocal = false, coninhcount = 1, connoinherit = true
> 
> The last part is confusing to me.

Yeah, I think it was patently wrong.  I changed it so that connoinherit
becomes true in this case.

Alvaro Herrera wrote:
> Jaime Casanova wrote:

> > also noted that if you:
> > 
> > """
> > create table t1(i int) partition by hash (i);
> > create table t1_0 partition of t1 for values with (modulus 2, remainder 0);
> > create table t1_1 partition of t1 for values with (modulus 2, remainder 1);
> > create unique index on t1(i);
> > alter table t1 add primary key using index t1_i_idx ;
> > """
> > 
> > the ALTER TABLE ADD PK does not recurse to partitions, which maybe is
> > perfectly fine because i'm using USING INDEX but it feels like an
> > oversight to me
> 
> Ouch.  Yeah, this is a bug.  I'll try to come up with something.

After looking at it for a few minutes I determined that adding this
feature requires some more work: you need to iterate on all partitions,
obtain the corresponding index, cons up a few fake parse nodes, then
recurse to create the PK in the children.  I think this should be doable
with a couple dozen lines of code, but it's a refinement that can be
added on top.  Care to submit a patch?  In the meantime, I added an
ereport(ERROR) to avoid leaving the system in an inconsistent state
(that probably would not even be reproduced correctly by pg_dump).

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