Re: using expression syntax for partition bounds

On 16/01/2019 08:41, Amit Langote wrote:
> OK, will change it back to partition_bound_expr. Removing "bound" from it
> makes the term ambiguous?

Yeah, let's leave it in.

> 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.

> 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.

>> I think that needs more refinement. In v8, the following errors
>>
>> CREATE TABLE t2 ( a name COLLATE "POSIX" ) PARTITION BY RANGE (a);
>> CREATE TABLE t2a PARTITION OF t2 FOR VALUES FROM (name 'foo') TO (name
>> 'xyz');
>> ERROR: collation of partition bound value for column "a" does not match
>> partition key collation "POSIX"
>>
>> The problem here is that the "name" type has a collation that is neither
>> the one of the column nor the default collation. We can allow that.
>
> 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.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services