Re: Adding support for Default partition in partitioning

On Wed, Jun 7, 2017 at 1:59 AM, amul sul <sulamul(at)gmail(dot)com> wrote:
> But Ashutosh's suggestion make sense, we might have constraints other
> than that partitioning constraint on default partition. If those
> constraints refutes the new partition's constraints, we should skip
> the scan.

Right. If the user adds a constraint to the default partition that is
identical to the new partition constraint, that should cause the scan
to be skipped.

Ideally, we could do even better. For example, if the user is
creating a new partition FOR VALUES IN (7), and the default partition
has CHECK (key != 7), we could perhaps deduce that the combination of
the existing partition constraint (which must certainly hold) and the
additional CHECK constraint (which must also hold, at least assuming
it's not marked NOT VALID) are sufficient to prove the new check
constraint. But I'm not sure whether predicate_refuted_by() is smart
enough to figure that out. However, it should definitely be smart
enough to figure out that if somebody's added the new partitioning
constraint as a CHECK constraint on the default partition, we don't
need to scan it.

The reason somebody might want to do that, just to be clear, is that
they could do this in multiple steps: first, add the new CHECK
constraint as NOT VALID. Then VALIDATE CONSTRAINT. Then add the new
non-default partition. This would result in holding an exclusive lock
for a lesser period of time than if they did it all together as one
operation.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company