citusdata / citusdata/citus

Changing the Data Type of Distbute Column in a Hash Rule Invariant Scenario

Open
#2,919 3 comments 0 reactions 0 assignees View on GitHub
feature warm-up
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

[Question]
In our production environment, due to the design problems of distribute column (too small in length), subsequent data can not be inserted properly.

```
app_db=# create table testdis1(c1 int, c2 varchar(30) primary key);
CREATE TABLE
app_db=# select create_distributed_table('testdis1','c2');
create_distributed_table
-----------------------------

(1 row)

app_db=# insert into testdis1 select 1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
ERROR: value too long for type character varying(30)
```

[try]
In this case, we want to change the data type of distribute column (varchar(30) -> varchar(50)),and the failed.
```
app_db=# alter table testdis1 alter c2 type varchar(50);
ERROR: cannot execute ALTER TABLE command involving partition column
app_db=#
```

[Ideas]
1. Redefine tables and import data into new tables. But since the total number of records is 49457,373,184, re-importing data can be time-consuming.

2. We believe that the hash rule algorithm of varchar(30) and varchar(50) should be the same. In this scenario, the data type of distribute column should be changed without re-publishing the data.

[hopes]
We hope that data type changes in Distbute Column in future versions of citus in scenarios where hash rules remain unchanged

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.