Re: Declarative partitioning

>On 2015-08-19 AM 02:57, Marc Mamin wrote:
>>> 2. Creating a partition of a partitioned table
>>>
>>> CREATE TABLE table_name
>>> PARTITION OF partitioned_table_name
>>> FOR VALUES values_spec;
>>>
>>> Where values_spec is:
>>>
>>> listvalues: [IN] (val1, ...)
>>>
>>
>> Would it make sense to allow one complementary partition to the listvalues?
>>
>> listvalues: [[NOT] IN] (val1, ...)
>>
>> I've thought a few times about moving data with some most common values to dedicated partitions
>> and keeping the rest in a separate one...
>>
>
>Thanks, that's definitely something to consider.
>
>I have been thinking of a sort of default list partition for the "rest" of
>values. Would you rather declare that with something like the below than
>having to enumerate all the values in a NOT IN list? Or the NOT IN way is
>more intuitive/friendly?
>
>CREATE TABLE _rest PARTITION OF table_name FOR VALUES [ IN ] DEFAULT
>
>Of course, at most one such partition would be allowed.

On the one hand I guess it will be easies to check for partition overlapping if their definitions all contain the exact allowed values.
But this could be generalized to range partitions too:

CREATE TABLE _rest FALLBACK PARTITION OF table_name

The need for it for range partitions seems very narrow at the first glimpse, but I remember bore administrative work in order to ensure that there always was a partition available for incoming data (from a very old time when I was still working with Oracle).

To have it comfortable and nevertheless allow to define new partitions, this would require to always check/move data from the default partition to new partitions at create time.

and 2 other thoughts:
- In your proposal, the parent table is not materialized at all. Could it be used for the fallback partition?
- what about always having a fallback partition? This would reduce the risk of unexpected failures and somewhat help Postgres stand out from the crowd :)

regards,
Marc Mamin