Re: using expression syntax for partition bounds

Hello.

At Fri, 18 Jan 2019 17:50:56 +0900, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote in <cbe3823f-f61e-1f37-c9ee-a3de9d8d565e(at)lab(dot)ntt(dot)co(dot)jp>
> Thanks for the comments.
>
> On 2019/01/18 16:48, Peter Eisentraut wrote:
> >> How about the following note in the documentation:
> >>
> >> + Although volatile expressions such as
> >> + <literal><function>CURRENT_TIMESTAMP</function></literal> can be used
> >> + for this, be careful when using them, because
> >> + <productname>PostgreSQL</productname> will evaluate them only once
> >> + when adding the partition.
> >
> > I don't think we have to phrase it in this warning way. Just say that
> > volatile expressions are evaluated at the time of the DDL statement.
>
> OK, then just this:
>
> + Even volatile expressions such as
> + <literal><function>CURRENT_TIMESTAMP</function></literal> can be used.

I agree to not to phrase in a warning way, but it seems
too-simplified. I think the reason is still required, like this?:

===
The expression is evaluated once at the table creation time so it
can involve even volatile expressions such as
<literal><function>CURRENT_TIMESTAMP</function></literal>.
===

> >> Sorry but I'm not sure how or what I would test about this. Maybe, just
> >> add a test in create_table.sql/alter_table.sql that shows that using
> >> volatile expression doesn't cause an error?
> >
> > Possibilities: Create a range partition with current_timestamp as the
> > upper bound and then in a separate transaction insert current_timestamp
> > and have it appear in the default partition. Or create list partition
> > with session_user as one partition's value and then insert session_user
> > and have it appear in that table. Or something like those.
>
> OK, added a test that uses current_timestamp.
>
> >> So, should the "name" type's collation should simply be discarded in favor
> >> of "POSIX" that's being used for partitioning?
> >
> > In that specific case, yes, I think so.
> >
> >>> What we don't want is someone writing an explicit COLLATE clause. I
> >>> think we just need to check that there is no top-level COLLATE clause.
> >>> This would then sort of match the logic parse_collate.c for combining
> >>> collations.
> >>
> >> Maybe I'm missing something, but isn't it OK to allow the COLLATE clause
> >> as long as it specifies the matching collation as the parent?
> >
> > Yes, that should be OK.
>
> Alright, I've included a test that uses cast expression in partition bound.
>
> Updated patch attached.

--
Kyotaro Horiguchi
NTT Open Source Software Center