Re: Partitioning with range types - Mailing list pgsql-general

On 06/15/2018 09:59 AM, Jeremy Finzel wrote:
> 
> 
> On Fri, Jun 15, 2018 at 11:23 AM, Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 06/15/2018 08:26 AM, Jeremy Finzel wrote:
> 
>         Several months ago we had some detailed discussions about
>         whether to use separate date columns to indicate a date range,
>         or to use the daterange data type.  We opted for the latter
>         because this type is specifically designed for this use case - a
>         table that has a range of valid dates for the data it contains. 
>         It also has some great operators and functions.
> 
>         But I recently discovered that daterange is not supported in any
>         way as a partition key because it depends on an expression.  I
>         was excited
> 
> 
>     A quick test:
> 
>     Postgres 10.4
> 
>     create table dr_partition(id integer, dr daterange) PARTITION BY
>     LIST(dr);
> 
>       \d dr_partition
>                   Table "public.dr_partition"
>       Column |   Type    | Collation | Nullable | Default
>     --------+-----------+-----------+----------+---------
>       id     | integer   |           |          |
>       dr     | daterange |           |          |
>     Partition key: LIST (dr)
> 
> 
>     create table dr_1 PARTITION OF dr_partition FOR VALUES IN
>     ('[06/01/2018, 06/30/2018]');
> 
>     \d dr_1
>                       Table "public.dr_1"
>       Column |   Type    | Collation | Nullable | Default
>     --------+-----------+-----------+----------+---------
>       id     | integer   |           |          |
>       dr     | daterange |           |          |
>     Partition of: dr_partition FOR VALUES IN ('[2018-06-01,2018-07-01)')
> 
> 
> 
>         about this possibility in pg11 with unique constraints on the
>         parent table, but now it appears it may have instead been to our
>         advantage if we had two separate date columns instead, so that
>         we could use UPSERT transparently for date-ranged tables.
> 
>         Is there any possibility of this feature coming for range types,
>         or, if we really want to partition using daterange, should we
>         look instead at two separate date columns?
> 
>         Thanks,
>         Jeremy
> 
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> Let me clarify - what I said was not accurate.  What I meant is that 
> using an UPSERT on a parent partition is not supported with range types 
> specifically because we can't create unique indexes involving 
> expressions on parent partitions:
> 
> CREATE UNIQUE INDEX ON foo (id, lower(as_of_date));
> ERROR:  unsupported UNIQUE constraint with partition key definition
> DETAIL:  UNIQUE constraints cannot be used when partition keys include 
> expressions.

Would it be possible to show all the schema involved in the above?

> 
> Workaround is of course not to use UPSERT, but we all know the 
> advantages of using UPSERT to handle concurrency and the like and to 
> make our queries simpler.  We are currently using UPSERT for many of 
> these tables, but they are not partitioned yet.
> 
> Thanks,
> Jeremy


-- 
Adrian Klaver
adrian.klaver@aklaver.com