Re: Declarative partitioning

From: David Fetter <david(at)fetter(dot)org>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Declarative partitioning
Date: 2015-08-19 12:52:39
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: 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:
> >
> >
> > 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

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!

David Fetter <david(at)fetter(dot)org>
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres:

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Verite 2015-08-19 13:02:59 Re: [patch] psql tab completion for grant execute
Previous Message Simon Riggs 2015-08-19 12:46:33 Re: allowing wal_level change at run time