Re: [COMMITTERS] pgsql: Implement table partitioning.

On Fri, Dec 9, 2016 at 1:23 PM, Keith Fiske <keith(at)omniti(dot)com> wrote:

>
>
> On Fri, Dec 9, 2016 at 1:13 PM, Amit Langote <amitlangote09(at)gmail(dot)com>
> wrote:
>
>> Hi Keith,
>>
>> On Sat, Dec 10, 2016 at 3:00 AM, Keith Fiske <keith(at)omniti(dot)com> wrote:
>> > Being that table partitioning is something I'm slightly interested in,
>> > figured I'd give it a whirl.
>> >
>> > This example in the docs has an extraneous comma after the second column
>> >
>> > CREATE TABLE cities (
>> > name text not null,
>> > population int,
>> > ) PARTITION BY LIST (initcap(name));
>> >
>> > And the WITH OPTIONS clause does not appear to be working using another
>> > example from the docs. Not seeing any obvious typos.
>> >
>> > keith(at)keith=# CREATE TABLE measurement_y2016m07
>> > keith-# PARTITION OF measurement (
>> > keith(# unitsales WITH OPTIONS DEFAULT 0
>> > keith(# ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
>> > 2016-12-09 12:51:48.728 EST [11711] ERROR: syntax error at or near
>> "WITH"
>> > at character 80
>> > 2016-12-09 12:51:48.728 EST [11711] STATEMENT: CREATE TABLE
>> > measurement_y2016m07
>> > PARTITION OF measurement (
>> > unitsales WITH OPTIONS DEFAULT 0
>> > ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
>> > ERROR: syntax error at or near "WITH"
>> > LINE 3: unitsales WITH OPTIONS DEFAULT 0
>> > ^
>> > Time: 0.184 ms
>> >
>> > Removing the unit_sales default allows it to work fine
>>
>> WITH OPTIONS keyword phrase is something that was made redundant in
>> the last version of the patch, but I forgot to remove the same in the
>> example. I've sent a doc patch to fix that.
>>
>> If you try - unitsales DEFAULT 0, it will work. Note that I did not
>> specify WITH OPTIONS.
>>
>> Thanks,
>> Amit
>>
>
> That works. Thanks!
>
> keith(at)keith=# CREATE TABLE measurement_y2016m07
> PARTITION OF measurement (
> unitsales DEFAULT 0
> ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
> CREATE TABLE
> Time: 4.091 ms
>
>
Working on a blog post for this feature and just found some more
inconsistencies with the doc examples. Looks like the city_id column was
defined in the measurements table when it should be in the cities table.
The addition of the partition to the cities table fails since it's missing.

Examples should look like this:

CREATE TABLE measurement (
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

CREATE TABLE cities (
city_id bigserial not null,
name text not null,
population int
) PARTITION BY LIST (initcap(name));

I actually changed my example to have city_id use bigserial to show that
sequences are inherited automatically. May be good to show that in the docs.

Another suggestion I had was for handling when data is inserted that
doesn't match any defined child tables. Right now it just errors out, but
in pg_partman I'd had it send the data to the parent instead to avoid data
loss. I know that's not possible here, but how about syntax to define a
child table as a "default" to take data that would normally be rejected?
Maybe something like

CREATE TABLE measurement_default
PARTITION OF measurement (
unitsales DEFAULT 0
) FOR VALUES DEFAULT;

Keith