>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
allowedvalues.
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
orderto ensure that there always was a partition available for incoming data (from a very old time when I was still
workingwith Oracle).
To have it comfortable and nevertheless allow to define new partitions, this would require to always check/move data
fromthe 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
Postgresstand out from the crowd :)
regards,
Marc Mamin
