citusdata / citusdata/citus

alter_distributed_table fails to restore materialized view when distribution column has a varchar type

Open
#5,157 0 comments 0 reactions 0 assignees View on GitHub
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

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.