Bad message for create partition when there is data

[jd-zhang]

Issue migrated from trac ticket # 24

component: computing nodes | priority: major

2021-04-15 11:48:50: @jd-zhang created the issue

---

tdb1=# CREATE TABLE range_parted2 (a int) PARTITION BY RANGE (a); CREATE TABLE tdb1=# CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT; CREATE TABLE tdb1=# INSERT INTO range_parted2 VALUES (85); INSERT 0 1 tdb1=# CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90); ERROR: could not open file "base/237568/532488": No such file or directory tdb1=# CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100); ERROR: could not open file "base/237568/532488": No such file or directory

For postgresql-12.5,t the message is like: tdb1=# CREATE TABLE range_parted2 (a int) PARTITION BY RANGE (a); CREATE TABLE tdb1=# CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT; CREATE TABLE tdb1=# INSERT INTO range_parted2 VALUES (85); INSERT 0 1 tdb1=# CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90); ERROR: updated partition constraint for default partition "range2_default" would be violated by some row tdb1=# CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100); CREATE TABLE

[jd-zhang]

2021-04-26 10:28:06: @jd-zhang commented

---

The real issue here is that, we can not add new partition if we have set the default partition.

The sql test code is:

tdb1=# create table pc_list_parted (a int) partition by list(a); CREATE TABLE tdb1=# create table pc_list_part_1 partition of pc_list_parted for values in (1); CREATE TABLE tdb1=# create table pc_list_part_2 partition of pc_list_parted for values in (2); CREATE TABLE tdb1=# create table pc_list_part_def partition of pc_list_parted default; CREATE TABLE tdb1=# create table pc_list_part_3 partition of pc_list_parted for values in (3); ERROR: could not open file "base/237568/714116": No such file or directory

[jd-zhang]

2021-04-27 16:27:10: @jd-zhang commented

---

similar case, but not create, using attach, and error happens at different places:

CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a); CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b); CREATE TABLE quuux_default1 PARTITION OF quuux_default FOR VALUES IN ('b'); CREATE TABLE quuux1 (a int, b text); ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate! CREATE TABLE quuux2 (a int, b text); ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation DROP TABLE quuux1; DROP TABLE quuux2; -- should validate for quuux1, but not for quuux2 CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1); CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);

psql:alter_table.sql:1276: ERROR: could not open file "base/13126/819721": No such file or directory psql:alter_table.sql:1277: ERROR: could not open file "base/13126/819721": No such file or directory

[jd-zhang]

2021-06-02 12:38:35: @david-zhao

[jd-zhang]

2021-06-02 12:38:35: @david-zhao commented

---

default partition can't be reliably used for now ---- after a default partition is created, when next partition created kunlun needs to check whether there are rows which match the new partition's boundary setting but are in the default partition, and the 'CREATE TABLE' stmt should fail if there are.

We could implement a remote check by pushing down a ' SELECT count(*) ' stmt after locking the default partition. But if the check doesn't fail, when we do 'create table' in the same connection, the lock is released automatically by MySQL and the default partition might be inserted rows by other computing nodes that should have been written to the new partition being created.

[jd-zhang]

2021-06-02 12:39:14: @david-zhao

[jd-zhang]

2021-06-02 16:58:02: @david-zhao commented

---

See [wiki:kunlun.features.design.advanced_partitioning] for more details.

[jd-zhang]

2021-06-02 16:58:29: @david-zhao

[jd-zhang]

2021-06-02 17:35:24: @david-zhao commented

---

DEFAULT partitioning is now disabled, and it can be enabled when above issue is solved.