Re: Declarative partitioning - Mailing list pgsql-hackers

On Wed, Aug 19, 2015 at 04:30:39PM +0900, Amit Langote wrote:
> 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!

I hope I'm advancing this feature rather than bogging it down...

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

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

Thanks so much for working on this!

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