Re: Partitioning with range types

On 06/15/2018 09:59 AM, Jeremy Finzel wrote:
>
>
> On Fri, Jun 15, 2018 at 11:23 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com