should check collations when creating partitioned index

Поиск
Список
Период
Сортировка
От Peter Eisentraut
Тема should check collations when creating partitioned index
Дата
Msg-id 3327cb54-f7f1-413b-8fdb-7a9dceebb938@eisentraut.org
обсуждение исходный текст
Ответы Re: should check collations when creating partitioned index
Re: should check collations when creating partitioned index
Список pgsql-hackers
When creating a partitioned index, the partition key must be a subset of 
the index's columns.  DefineIndex() explains:

      * If this table is partitioned and we're creating a unique index, 
primary
      * key, or exclusion constraint, make sure that the partition key is a
      * subset of the index's columns.  Otherwise it would be possible to
      * violate uniqueness by putting values that ought to be unique in
      * different partitions.

But this currently doesn't check that the collations between the 
partition key and the index definition match.  So you can construct a 
unique index that fails to enforce uniqueness.

Here is a non-partitioned case for reference:

create collation case_insensitive (provider=icu, 
locale='und-u-ks-level2', deterministic=false);
create table t0 (a int, b text);
create unique index i0 on t0 (b collate case_insensitive);
insert into t0 values (1, 'a'), (2, 'A');  -- violates unique constraint

Here is a partitioned case that doesn't work correctly:

create table t1 (a int, b text) partition by hash (b);
create table t1a partition of t1 for values with (modulus 2, remainder 0);
create table t1b partition of t1 for values with (modulus 2, remainder 1);
create unique index i1 on t1 (b collate case_insensitive);
insert into t1 values (1, 'a'), (2, 'A');  -- this succeeds

The attached patch adds the required collation check.  In the example, 
it would not allow the index i1 to be created.
Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: yuansong
Дата:
Сообщение: Re:Re: How to solve the problem of one backend process crashing and causing other processes to restart?
Следующее
От: "Drouvot, Bertrand"
Дата:
Сообщение: Re: Synchronizing slots from primary to standby