David Rowley <david.row...@2ndquadrant.com> writes:
> I imagined this would have had a check for volatile functions and some
> user-friendly error message to say partition bounds must be immutable,
> but instead, it does:
> postgres=# create table d_p1 partition of d for values in (Random());
> ERROR: specified value cannot be cast to type double precision for column "d"
> LINE 1: create table d_p1 partition of d for values in (Random());
> ^
> DETAIL: The cast requires a non-immutable conversion.
> HINT: Try putting the literal value in single quotes.
> For inspiration, maybe you could follow the lead of CREATE INDEX:
> postgres=# create index on d ((random()));
> ERROR: functions in index expression must be marked IMMUTABLE
Well, that just begs the question: why do these expressions need to
be immutable? What we really want, I think, is to evaluate them
and reduce them to constants. After that, it hardly matters whether
the original expression was volatile. I see absolutely no moral
difference between "for values in (random())" and cases like
this, which works today:
regression=# create table pp(d1 date) partition by range(d1);
CREATE TABLE
regression=# create table cc partition of pp for values from ('today') to
('tomorrow');
CREATE TABLE
regression=# \d+ cc
Table "public.cc"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Descr
iption
--------+------+-----------+----------+---------+---------+--------------+------
-------
d1 | date | | | | plain | |
Partition of: pp FOR VALUES FROM ('2018-04-10') TO ('2018-04-11')
Partition constraint: ((d1 IS NOT NULL) AND (d1 >= '2018-04-10'::date) AND (d1 <
'2018-04-11'::date))
If we're willing to reduce 'today'::date to a fixed constant,
why not random()?
regards, tom lane
