Adding a Partition in Painful

Folks,

While it would be lovely if we could all plan all table partitioning
in advance of inserting the first row, that's not the reality, and has
little prospect of becoming the reality. One reality I'm dealing with
is where people discover the list partitions they need to add based on
the contents of the table. This is common in cases of multi-tenancy
where (as usual) tenant row counts are far from uniform.

I'd like to make it easier to add a partition to an existing table
when a default partition (or others, but let's keep this scope
reasonable in size) already has lots of rows in it, some of which
would need to move to the new partition. At the moment, this operation
fails with an error message that's not super helpful in moving
forward.

To deal with this situation, I'd like to make some proposals for
functionality that would fix this

1.

CREATE TABLE ... PARTITION OF ... NOT VALID/
ALTER TABLE ... ATTACH PARTITION ... NOT VALID

This would create (or attach) a partition, immediately start routing
new and updated tuples to it, and would ignore any matching tuples in
the default partition for the purposes of completing. Obviously, this
situation would require manual cleanup steps. It would also require an
option along the lines of a new command:

ALTER TABLE ... VALIDATE.

This would scan the default partition with all the attendant "fun."

1a. Add an option along the lines of ALTER TABLE ... VALIDATE FORCE

With FORCE, it would simply mark the partition as valid in the
catalog, allowing for the possibility of pilot error, but requiring
only a brief lock.

2.
CREATE TABLE CONCURRENTLY ... PARTITION OF .../
ALTER TABLE CONCURRENTLY ... ATTACH PARTITION ...

This would also make an "INVALID" partition as above, move the tuples
in the background, and set it to VALID upon completion of the task, as
CREATE INDEX CONCURRENTLY does now.

2a. Make CREATE TABLE ... PARTITION OF / ALTER TABLE ... ATTACH
PARTITION have the above behavior.

3. Unmodified commands that do the tuple-moving themselves while
holding an AEL. I'm pretty sure this one's a non-starter, but I wanted
to mention it for completeness.

What say?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate