Re: Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 12:17 PM, steven king <vacuum(at)quantentunnel(dot)de> wrote:
> the idea is, that this statement does not create a new kind of db-object. this "CREATE PARTITIONS" statement should be a meta-statement -> "meta" like the serial type.

That was my assumption as well.

> with create paritions you can create all defined tables (maybe by inheritin from <table>) at once.
>
> but you're right - why this statement should not be able to define some other table-properties (except columns)?!

I am of the opinion that defining partitions is a sufficiently
heavyweight operation that no one should worry too much about whether
or not each one needs to be created separately. As Greg Stark pointed
out, it's a lot more interesting to try to figure out how it's
actually going to be implemented. We can invent pretty syntax after
the fact easily enough.

> please notice ... the statement below was only a draft - to show the intention. the major difference is the usage of conditions .. this could lead into creation of triggers with a lot of PL/pqSQL code or invocations of built-in functions.

Sure, I'm just providing my feedback, since, hey, someone posted to
the mailing list and asked for input...

I am not sold on the "CASE" method of defining partitions. It seems
to me that one of the issues that needs to be tackled is how to select
a partition efficiently, and I don't think CASE is going to be ideal
for that, because it presupposes an iterative model working down from
top to bottom. If there are 1000 partitions and the conditions are
complex, that could start to add up to significant overhead.

I think we should aim to set up range partitioning using a data
structure that is amenable to binary search. For example, you might
think of keeping a sorted array of length N of values (perhaps a row
type if a composite key is being used) and an array of length N+1 of
partitions. Now you can use binary search to find the first value in
the list which is greater than the key (if any) and then look up that
index in the second list to figure out where to put the tuple.

Now, you might think that's a bad design... you're welcome to propose
your own. But I think the design should come first and the syntax
afterward.

> open your mind :)

Not trying to be close-minded...

...Robert