Re: Partitioning WIP patch - Mailing list pgsql-hackers

On 26-02-2015 AM 09:28, Jim Nasby wrote:
> On 2/24/15 2:13 AM, Amit Langote wrote:
>> -- a plain table
>> CREATE TABLE parent_monthly(year int, month int, day int);
>>
>> -- a partitioned table
>> -- xxxxx: number of partitions
>> CREATE TABLE parent_monthly_xxxxx(LIKE parent_monthly) PARTITION BY
>> RANGE ON(year, month);
> 
> To be clear, in this example parent_table_xxxxx is in no way related to
> parent_monthly, just like a normal CREATE TABLE (LIKE table), right?
> 

Yes, there is no relation at all. I was maybe just trying to save few
keystrokes. Sorry, that may be confusing.

parent_monthly is just a regular table, part of the example.

>> -- partitions
>> CREATE TABLE parent_monthly_xxxxx_201401 PARTITION OF
>> parent_monthly_00100_201401 FOR VALUES BETWEEN (2014, 1) AND (2014, 2);
> 
> And the partitions are still inheritance children?
> 

At this point, they *almost* are; more for the sake of Append. Though
sooner than later, we will have to invent a version of Append for
partitioned tables that does more than just append the outputs of
underlying plans. For example, it would use partitioninfo cached in
relation descriptor of the parent to drive partition-pruning for
starters. Pruning child relations individually by way of
constraint_exclusion doesn't scale as is well known.

To clarify things a bit more, transformCreateStmt() transforms PARTITION
OF into LIKE INCLUDING ALL, not INHERITS. And as mentioned, for Append
to work, I have made ATExecAddInherit() to do some of the things
ATExecAttachPartition() does. Again, that is a temporary arrangement.

> Does ALTER TABLE parent_monthly_xxxxx_201401 ADD COLUMN foo still
> operate the same as today? I'd like to see us continue to support that,
> but perhaps it would be wise to not paint ourselves into that corner
> just yet.

Nothing prevents that from working, at least at the moment.

CREATE TABLE parent_monthly_00012(LIKE parent_monthly) PARTITION BY
RANGE ON(year, month);

CREATE TABLE parent_monthly_00012_201401 PARTITION OF
parent_monthly_00012 FOR VALUES BETWEEN (2014, 1) AND (2014, 2);

<snip>

CREATE TABLE parent_monthly_00012_201412 PARTITION OF
parent_monthly_00012 FOR VALUES BETWEEN (2014, 12) AND (2015, 1);

# INSERT INTO parent_monthly_00012 VALUES (2014, 07, 01);
INSERT 0 1

# SELECT * FROM parent_monthly_00012;year | month | day
------+-------+-----2014 |     7 |   1
(1 row)

# INSERT INTO parent_monthly_00012 VALUES (2014, 08, 01);
INSERT 0 1

# ALTER TABLE parent_monthly_00012_201408 ADD COLUMN hour int;
ALTER TABLE

# INSERT INTO parent_monthly_00012_201408 VALUES (2014, 08, 01, 10);
INSERT 0 1

# SELECT * FROM parent_monthly_00012;year | month | day
------+-------+-----2014 |     7 |   12014 |     8 |   12014 |     8 |   1
(3 rows)

# SELECT * FROM parent_monthly_00012_201408;year | month | day | hour
------+-------+-----+------2014 |     8 |   1 |2014 |     8 |   1 |   10
(2 rows)

Thanks,
Amit