From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: David Fetter <david@fetter.org>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Declarative partitioning
Date: Wed, 19 Aug 2015 16:30:39 +0900
Message-ID: <55D4309F.8030601@lab.ntt.co.jp> (raw)
In-Reply-To: <20150818134323.GC18054@fetter.org>
References: <55D3093C.5010800@lab.ntt.co.jp>
<20150818134323.GC18054@fetter.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
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-hackers@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, david@fetter.org
Subject: Re: Declarative partitioning
In-Reply-To: <55D4309F.8030601@lab.ntt.co.jp>
* 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