Re: Declarative partitioning

On 2015-08-19 PM 09:52, David Fetter wrote:
> On Wed, Aug 19, 2015 at 04:30:39PM +0900, Amit Langote wrote:
>>>
>>> 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!
>
> I hope I'm advancing this feature rather than bogging it down...
>

Definitely advancing.

>> 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.
>
> Well, I was assuming an exact match, but a stricter match seems like a
> nice-to-have...possibly later.
>
>>> 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?
>
> There are use cases where we need to warn people that their assertions
> need to be true, and if those assertions are not true, this will
> explode, leaving them to pick the resulting shrapnel out of their
> faces. There are other parts of the system where this is true, as
> when people write UDFs in C.
>
> As I understand it, NOT VALID means, "I assert that the tuples already
> here fit the constraint. Any changes will be checked against the
> constraint."
>
> I've seen cases where a gigantic amount of data is coming out of some
> distributed system which holds the constraint as an invariant. This
> let a DBA decide to add a NOT VALID constraint in order not to take
> the hit of a second full scan of the data, which might have made the
> import, and possibly the entire project, untenable.
>
> See above.
>

Ah, I understand the point of parameterization (TRUST). Seems like it
would be good to have with appropriate documentation of the same. Perhaps,
it might as well a parameter to the step 1 itself.

>>>> 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.
>
> So no. Weakening required locks has been something of an ongoing
> project, project-wide, and need not be part of the first cut of this
> long-needed feature.
>

Do you mean ATTACH and DETACH, if they require access exclusive lock on
the parent, should not be in the first cut? Or am I misreading?

If so, there is no way to drop partitions. With the patch, it would be
achieved with detach and drop (if required).

> Thanks so much for working on this!
>

Thanks for the feedback and suggestions!

Regards,
Amit