Re: Boolean partitions syntax

At Wed, 11 Apr 2018 14:22:29 +0900, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote in
<6e929961-4160-7338-3d26-ccf84f41672a@lab.ntt.co.jp>
> On 2018/04/11 13:39, David Rowley wrote:
> > On 11 April 2018 at 05:22, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> David Rowley <david.rowley@2ndquadrant.com> writes:
> >>> On 11 April 2018 at 03:34, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>>> 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:
> >>
> >>> I'd personally be pretty surprised if this worked.
> >>
> >> Well, my point is that we're *already* behaving that way in some cases,
> >> simply because of the existence of macro-like inputs for some of these
> >> datatypes.  I'm not sure that users are going to perceive a big difference
> >> between 'now'::timestamptz and now(), for example.  If we take one but
> >> not the other, I don't think anybody will see that as a feature.
> > 
> > To me, it seems a bit inconsistent to treat 'now'::timestamp and now()
> > the same way.
> > 
> > I found this in the 7.4 release notes [1]:
> > 
> > "String literals specifying time-varying date/time values, such as
> > 'now' or 'today' will no longer work as expected in column default
> > expressions; they now cause the time of the table creation to be the
> > default, not the time of the insertion. Functions such as now(),
> > current_timestamp, or current_dateshould be used instead.
> > 
> > In previous releases, there was special code so that strings such as
> > 'now' were interpreted at INSERT time and not at table creation time,
> > but this work around didn't cover all cases. Release 7.4 now requires
> > that defaults be defined properly using functions such as now() or
> > current_timestamp. These will work in all situations."
> > 
> > So isn't 'now' being different from now() in DDL something users
> > should be quite used to by now?
> > 
> > I've got to admit, I'm somewhat less concerned about evaluating
> > volatile functions in this case because you don't seem that concerned,
> > but if you happen to be wrong, then it's going to be a much harder
> > thing to fix.  Really, is anyone going to complain if we don't
> > evaluate these and reject them with an error instead? It seems like a
> > safer option to me, also less work, and we're probably less likely to
> > regret it.

That is found in the current documentation.

https://www.postgresql.org/docs/devel/static/datatype-datetime.html

(now, today and so)
|  are simply notational shorthands that will be converted to
|  ordinary date/time values when read.

> As someone said upthread, we should just document that we *always*
> evaluate the expression specified for a partition bound during create
> table and not some other time.  That seems easier than figuring out what
> to say in the error message; saying "cannot use immutable expression for
> partition bound" is likely to confuse a user even more by introducing the
> ambiguity about when partition bounds are evaluated.  Most users would
> expect it to be create table time anyway.

+1

> > We also need to decide what of this we can backpatch to PG10 to fix
> > [2].  Ideally what goes into PG10 and PG11 would be the same, so
> > perhaps that's another reason to keep it more simple.
> 
> Backpatch all of it?  Newly introduced syntax and evaluation semantics
> does not break inputs that PG 10 allows.  But I may be missing something.

My understanding is that it is not back-patchable since it
introduces different behavior explicitly mentioned in
documentation.

https://www.postgresql.org/docs/10/static/sql-createtable.html

| and partition_bound_spec is:
| 
| IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
| FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
|   TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )

Boolean literals are explicitly excluded. If we back-port only
the boolean literal stuff, documentation will need updated.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center