alter_distributed_table fails to restore materialized view when distribution column has a varchar type
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Considering the following SQL:
```postgresql
CREATE TABLE test_1(user_id VARCHAR);
SELECT create_distributed_table('test_1', 'user_id');
CREATE TABLE test_2(user_id VARCHAR);
SELECT create_distributed_table('test_2', 'user_id');
CREATE MATERIALIZED VIEW test_view AS
SELECT test_1.user_id uid1, test_2.user_id uid2 FROM test_1 INNER JOIN test_2 ON test_1.user_id = test_2.user_id;
SELECT alter_distributed_table(table_name := 'test_1', shard_count := 64, cascade_to_colocated := false);
```
It fails with
```
[0A000] ERROR: complex joins are only supported when all distributed tables are joined on their distribution columns with equal operator
```
Because the mat view code in the `pg_matviews` casts the column to `text` type:
```
SELECT test_1.user_id AS uid1,
test_2.user_id AS uid2
FROM (test_1
JOIN test_2 ON (((test_1.user_id)::text = (test_2.user_id)::text)));
```
Contributor guide
Assessment
This issue has not been assessed yet.