Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M279533=pgsql-hackers-arkari@postgresql.org>)
 id 1ZRhC9-0000g9-2G
 for pgsql-hackers@arkaria.postgresql.org; Tue, 18 Aug 2015 13:44:53 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-hackers-owner+M279533=pgsql-hackers-arkari@postgresql.org>)
 id 1ZRhC8-0004eI-Ju
 for pgsql-hackers@arkaria.postgresql.org; Tue, 18 Aug 2015 13:44:52 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <shackle@fetter.org>) id 1ZRhAq-00034P-UN
 for pgsql-hackers@postgresql.org; Tue, 18 Aug 2015 13:43:32 +0000
Received: from 173-164-140-181-sfba.hfc.comcastbusiness.net ([173.164.140.181]
 helo=fetter.org) by magus.postgresql.org with esmtp (Exim 4.84)
 (envelope-from <shackle@fetter.org>) id 1ZRhAn-0007Tx-PB
 for pgsql-hackers@postgresql.org; Tue, 18 Aug 2015 13:43:32 +0000
Received: by fetter.org (Postfix, from userid 500)
 id D445962615; Tue, 18 Aug 2015 06:43:23 -0700 (PDT)
Date: Tue, 18 Aug 2015 06:43:23 -0700
From: David Fetter <david@fetter.org>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Declarative partitioning
Message-ID: <20150818134323.GC18054@fetter.org>
References: <55D3093C.5010800@lab.ntt.co.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <55D3093C.5010800@lab.ntt.co.jp>
User-Agent: Mutt/1.5.23 (2014-03-12)
X-Pg-Spam-Score: -0.9 (/)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Tue, Aug 18, 2015 at 07:30:20PM +0900, Amit Langote wrote:
> Hi,
> 
> I would like propose $SUBJECT for this development cycle. Attached is a
> WIP patch that implements most if not all of what's described below. Some
> yet unaddressed parts are mentioned below, too. I'll add this to the CF-SEP.

Thanks for pushing this forward!  We've needed this done for at least
a decade.

> 4. (yet unimplemented) Attach partition (from existing table)
> 
> ALTER TABLE partitioned_table
> ATTACH PARTITION partition_name
> FOR VALUES values_spec
> USING [TABLE] table_name;
> 
> ALTER TABLE table_name
> SET VALID PARTITION OF <parent>;
> 
> The first of the above pair of commands would attach table_name as a (yet)
> 'invalid' partition of partitioned_table (after confirming that it matches
> the schema and does not overlap with other partitions per FOR VALUES
> spec). It would also record the FOR VALUES part in the partition catalog
> and set pg_class.relispartition to true for table_name.
> 
> After the first command is done, the second command would take exclusive
> lock on table_name, scan the table to check if it contains any values
> outside the boundaries defined by FOR VALUES clause defined previously,
> throw error if so, mark as valid partition of parent if not.

One small change to make this part more efficient:

1. Take the access exclusive lock on table_name.
2. Check for a matching constraint on it.
3. If it's there, mark it as a valid partition.
4. If not, check for values outside the boundaries as above.

Should the be a *valid* constraint?  Perhaps that should be
parameterized, as I'm not yet seeing a compelling argument either
direction.  I'm picturing something like:

    ALTER TABLE table_name SET VALID PARTITION OF <parent> [TRUST]

where TRUST would mean that an existing constraint need not be VALID.

> Does that make sense?

Yep.

> 5. Detach partition
> 
> ALTER TABLE partitioned_table
> DETACH PARTITION partition_name [USING table_name]
> 
> This removes partition_name as partition of partitioned_table. The table
> continues to exist with the same name or 'table_name', if specified.
> pg_class.relispartition is set to false for the table, so it behaves like
> a normal table.

Could this take anything short of an access exclusive lock on the
parent?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers