Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M279611=pgsql-hackers-arkari@postgresql.org>)
 id 1ZRxsW-0006mz-EW
 for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Aug 2015 07:33:44 +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+M279611=pgsql-hackers-arkari@postgresql.org>)
 id 1ZRxsV-0005XC-Nu
 for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Aug 2015 07:33:43 +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 <Langote_Amit_f8@lab.ntt.co.jp>)
 id 1ZRxrE-00044N-Ao
 for pgsql-hackers@postgresql.org; Wed, 19 Aug 2015 07:32:24 +0000
Received: from tama50.ecl.ntt.co.jp ([129.60.39.147])
 by magus.postgresql.org with esmtp (Exim 4.84)
 (envelope-from <Langote_Amit_f8@lab.ntt.co.jp>) id 1ZRxr9-0002lQ-Fc
 for pgsql-hackers@postgresql.org; Wed, 19 Aug 2015 07:32:23 +0000
Received: from vc2.ecl.ntt.co.jp (vc2.ecl.ntt.co.jp [129.60.86.154])
 by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id t7J7WECW018399;
 Wed, 19 Aug 2015 16:32:14 +0900
Received: from vc2.ecl.ntt.co.jp (localhost [127.0.0.1])
 by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 569625F593;
 Wed, 19 Aug 2015 16:32:14 +0900 (JST)
Received: from imail3.m.ecl.ntt.co.jp (imail3.m.ecl.ntt.co.jp [129.60.5.248])
 by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 48A3E5F592;
 Wed, 19 Aug 2015 16:32:14 +0900 (JST)
Received: from [127.0.0.1] ([129.60.241.61])
 by imail3.m.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id t7J7W9DK009542;
 Wed, 19 Aug 2015 16:32:14 +0900
Subject: Re: Declarative partitioning
To: David Fetter <david@fetter.org>
References: <55D3093C.5010800@lab.ntt.co.jp>
 <20150818134323.GC18054@fetter.org>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Message-ID: <55D4309F.8030601@lab.ntt.co.jp>
Date: Wed, 19 Aug 2015 16:30:39 +0900
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <20150818134323.GC18054@fetter.org>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
X-TM-AS-MML: disable
X-Pg-Spam-Score: -3.2 (---)
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 2015-08-18 PM 10:43, David Fetter wrote:
>>
>> 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.
> 

That's an interesting idea. Thanks!

By a matching constraint, I guess you mean a 'valid' constraint from which
the declared partition constraint can be proven to follow. For (a simple)
example, from a CHECK (a >= 100 AND a < 150) on table_name, the partition
constraint implied by FOR VALUES START (100) END (200) can be assumed to hold.

> 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.
> 

Hmm, I'd think this step must be able to assert the partition constraint
beyond any doubt. If the DBA added the constraint and marked it invalid,
she should first VALIDATE the constraint to make it valid by performing
whatever steps necessary before. IOW, a full heap scan at least once is
inevitable (the reason why we might want to make this a two step process
at all). Am I missing something?

> 
>> 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?
> 

Yes, both the step 1 of ATTACH command and DETACH command take access
exclusive lock on the parent. They are rather quick metadata changes, so
should not stall others significantly, I think.

Thanks,
Amit



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