Re: What needs to be done for real Partitioning?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> > A command to remove a partition from the partitioned table and turn it into
> > a regular table.
>
> Ugh. Why? You can access the table directly anyway.
>
> > A command to take a regular table and turn it into a partition.
>
> Double ugh. Verifying that the table matches the partition scheme seems
> like a lot of ugly, bug-prone, unnecessary code. What's the use case
> for this anyway?
>
> Those last two are *certainly* not Phase I requirements, and I don't
> think we need them at all ever.

These are effectively equivalent to "ALTER TABLE RENAME". Without these
commands you would be in pretty much the same position as a DBA without the
ability to rename tables.

The heart of partitioned tables is being able to load and unload entire
partitions quickly. You have to have somewhere to "unload" them too. Most
people aren't happy just watching their data disappear entirely. They want to
move them other tables or even other databases.

Similarly, they have to have somewhere to load them from. They're usually not
happy loading data directly into their production data warehouse tables
without manipulating the data, or doing things like clustering or indexing.

You could argue for some sort of setup where you could take a partition
"offline" during which you could safely do things like export or manipulate
the data. But that's awfully limiting. What if I want to do things like add
columns, or change data types, or any other manipulation that breaks the
symmetry with the production partitioned table.

I don't think it's really hard at all to check that the table matches the
partition scheme. You can just require that there be an existing table
constraint in place that matches the partitioning scheme. I think you can even
be fascist about the exact syntax of the constraint fitting precisely a
specified format.

--
greg