Re: On partitioning

Amit Kapila wrote:
> On Tue, Dec 9, 2014 at 1:42 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > On Mon, Dec 8, 2014 at 2:56 PM, Andres Freund <andres(at)2ndquadrant(dot)com>
> wrote:
> > >> I don't think that's mutually exclusive with the idea of
> > >> partitions-as-tables. I mean, you can add code to the ALTER TABLE
> > >> path that says if (i_am_not_the_partitioning_root) ereport(ERROR, ...)
> > >> wherever you want.
> > >
> > > That'll be a lot of places you'll need to touch. More fundamentally: Why
> > > should we name something a table that's not one?
> >
> > Well, I'm not convinced that it isn't one. And adding a new relkind
> > will involve a bunch of code churn, too. But I don't much care to
> > pre-litigate this: when someone has got a patch, we can either agree
> > that the approach is OK or argue that it is problematic because X. I
> > think we need to hammer down the design in broad strokes first, and
> > I'm not sure we're totally there yet.
>
> That's right, I think at this point defining the top level behaviour/design
> is very important to proceed, we can decide about the better
> implementation approach afterwards (may be once initial patch is ready,
> because it might not be a major work to do it either way). So here's where
> we are on this point till now as per my understanding, I think that direct
> operations should be prohibited on partitions, you think that they should be
> allowed and Andres think that it might be better to allow direct operations
> on partitions for Read.

FWIW in my original proposal I was rejecting some things that after
further consideration turn out to be possible to allow; for instance
directly referencing individual partitions in COPY. We could allow
something like

COPY lineitems PARTITION FOR VALUE '2000-01-01' TO STDOUT
or maybe
COPY PARTITION FOR VALUE '2000-01-01' ON TABLE lineitems TO STDOUT

and this would emit the whole partition for year 2000 of table
lineitems, and only that (the value is just computed on the fly to fit
the partitioning constraints for that individual partition). Then
pg_dump would be able to dump each and every partition separately.

In a similar way we could have COPY FROM allow input into individual
partitions so that such a dump can be restored in parallel for each
partition.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services