PostgreSQL-13:重叠范围的分区

Question

我有一个黑名单，其中某些字符串以字符'0'，'1'，'2'，...，'9'，'a'，'b'，'c'，'d'，'e'，'f'，'?‘开头。

然后创建下表：

CREATE TABLE blacklist (concat TEXT UNIQUE NOT NULL) PARTITION BY RANGE (concat);

现在我做范围划分：

CREATE TABLE IF NOT EXISTS blacklist_list_0 PARTITION OF blacklist FOR VALUES FROM ('0') TO ('0ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_0 ADD CONSTRAINT blacklist_list_0 CHECK (concat >= '0' AND concat <= '0ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_1 PARTITION OF blacklist FOR VALUES FROM ('1') TO ('1ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_1 ADD CONSTRAINT blacklist_list_1 CHECK (concat >= '1' AND concat <= '1ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_2 PARTITION OF blacklist FOR VALUES FROM ('2') TO ('2ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_2 ADD CONSTRAINT blacklist_list_2 CHECK (concat >= '2' AND concat <= '2ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_3 PARTITION OF blacklist FOR VALUES FROM ('3') TO ('3ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_3 ADD CONSTRAINT blacklist_list_3 CHECK (concat >= '3' AND concat <= '3ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_4 PARTITION OF blacklist FOR VALUES FROM ('4') TO ('4ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_4 ADD CONSTRAINT blacklist_list_4 CHECK (concat >= '4' AND concat <= '4ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_5 PARTITION OF blacklist FOR VALUES FROM ('5') TO ('5ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_5 ADD CONSTRAINT blacklist_list_5 CHECK (concat >= '5' AND concat <= '5ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_6 PARTITION OF blacklist FOR VALUES FROM ('6') TO ('6ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_6 ADD CONSTRAINT blacklist_list_6 CHECK (concat >= '6' AND concat <= '6ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_7 PARTITION OF blacklist FOR VALUES FROM ('7') TO ('7ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_7 ADD CONSTRAINT blacklist_list_7 CHECK (concat >= '7' AND concat <= '7ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_8 PARTITION OF blacklist FOR VALUES FROM ('8') TO ('8ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_8 ADD CONSTRAINT blacklist_list_8 CHECK (concat >= '8' AND concat <= '8ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_9 PARTITION OF blacklist FOR VALUES FROM ('9') TO ('9ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_9 ADD CONSTRAINT blacklist_list_9 CHECK (concat >= '9' AND concat <= '9ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_a PARTITION OF blacklist FOR VALUES FROM ('a') TO ('affffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_a ADD CONSTRAINT blacklist_list_a CHECK (concat >= 'a' AND concat <= 'affffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_b PARTITION OF blacklist FOR VALUES FROM ('b') TO ('bffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_b ADD CONSTRAINT blacklist_list_b CHECK (concat >= 'b' AND concat <= 'bffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_c PARTITION OF blacklist FOR VALUES FROM ('c') TO ('cffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_c ADD CONSTRAINT blacklist_list_c CHECK (concat >= 'c' AND concat <= 'cffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_d PARTITION OF blacklist FOR VALUES FROM ('d') TO ('dffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_d ADD CONSTRAINT blacklist_list_d CHECK (concat >= 'd' AND concat <= 'dffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_e PARTITION OF blacklist FOR VALUES FROM ('e') TO ('effffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_e ADD CONSTRAINT blacklist_list_e CHECK (concat >= 'e' AND concat <= 'effffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_f PARTITION OF blacklist FOR VALUES FROM ('f') TO ('fffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_f ADD CONSTRAINT blacklist_list_f CHECK (concat >= 'f' AND concat <= 'fffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_rest PARTITION OF blacklist FOR VALUES FROM ('?') TO ('?ffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
ALTER TABLE blacklist_list_rest ADD CONSTRAINT blacklist_list_rest CHECK (concat >= '?' AND concat <= '?ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );

我在带有postgres 13.1的Ubuntu 20.04上得到以下错误消息：

postgres=# CREATE TABLE IF NOT EXISTS blacklist_list_rest PARTITION OF blacklist FOR VALUES FROM ('?') TO ('?ffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
ERROR:  partition "blacklist_list_rest" would overlap partition "blacklist_list_0"

但是当我在我的开发者工作站(gentoo linux，postgres 13.1)上运行它时，我没有任何问题。

有没有人知道怎么解决这个问题？我不认为这些字符重叠，我也不认为我遗漏了正则表达式之类的东西('?‘应该被检测为该场景中的字符)。

Answer 1

Postgres使用来自操作系统的排序规则，因此在不同的操作系统上比较和排序字符串可能会有所不同(甚至在同一系统上使用updates are applied时也是如此)。您可以强制与“纯”ASCII进行比较，而不使用使用"C"排序规则的排序规则：

CREATE TABLE blacklist 
(
  concat TEXT UNIQUE NOT NULL collate "C"
)
PARTITION BY RANGE (concat);