From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, jonathan(dot)katz(at)excoventures(dot)com, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, David Steele <david(at)pgmasters(dot)net>, Andres Freund <andres(at)anarazel(dot)de>, Robert Haas <robertmhaas(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Mark Dilger <hornschnorter(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Boolean partitions syntax |
Date: | 2018-04-10 15:34:17 |
Message-ID: | 22534.1523374457@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
David Rowley <david(dot)rowley(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Teodor Sigaev | 2018-04-10 15:36:00 | Re: Partitioned tables and covering indexes |
Previous Message | Jeremy Finzel | 2018-04-10 15:32:00 | Including SQL files in extension scripts |