Citus marks varchars with different lengths as co-located, but doesn't support INSERT..SELECT pushdown
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Reported by @saicitus.
This might not be a bug, but it's good to have a discussion around it.
`varchar(10)` and `varchar(30)` has the same `vartype` and thus hash function. So, Citus just marks them colocated. But, INSERT..SELECT pushdown rejects the queries that selects from one and inserts into the other.
```SQL
create table v10 (key varchar(10));
create table v30 (key varchar(30));
SELECT create_distributed_table('v10', 'key');
SELECT create_distributed_table('v30', 'key');
SELECT logicalrelid, colocationid FROM pg_dist_partition WHERE logicalrelid IN ('v10'::regclass, 'v30'::regclass);
┌──────────────┬──────────────┐
│ logicalrelid │ colocationid │
├──────────────┼──────────────┤
│ v10 │ 3 │
│ v30 │ 3 │
└──────────────┴──────────────┘
(2 rows)
```
However, `INSERT..SELECT` pushdown rejects the query and `INSERT..SELECT via coordinator kicks in`:
```SQL
INSERT INTO v10 SELECT * FROM v30;
DEBUG: cannot perform distributed INSERT INTO ... SELECT because the partition columns in the source table and subquery do not match
DETAIL: The data type of the target table's partition column should exactly match the data type of the corresponding simple column reference in the subquery.
DEBUG: Collecting INSERT ... SELECT results on coordinator
```
Because PostgreSQL adds an implicit coercion to the SELECT target entry:
```
(FuncExpr) $11 = {
xpr = (type = T_FuncExpr)
funcid = 669
funcresulttype = 1043
funcretset = false
funcvariadic = false
funcformat = COERCE_IMPLICIT_CAST
funccollid = 100
inputcollid = 100
args = 0x00007fee9d856d40
location = -1
}
```
Where:
```SQL
SELECT * FROM pg_proc where oid = 669;
┌─[ RECORD 1 ]────┬───────────────────┐
│ proname │ varchar │
│ pronamespace │ 11 │
│ proowner │ 10 │
│ prolang │ 12 │
│ procost │ 1 │
│ prorows │ 0 │
│ provariadic │ 0 │
│ protransform │ varchar_transform │
│ prokind │ f │
│ prosecdef │ f │
│ proleakproof │ f │
│ proisstrict │ t │
│ proretset │ f │
│ provolatile │ i │
│ proparallel │ s │
│ pronargs │ 3 │
│ pronargdefaults │ 0 │
│ prorettype │ 1043 │
│ proargtypes │ 1043 23 16 │
│ proallargtypes │ │
│ proargmodes │ │
│ proargnames │ │
│ proargdefaults │ │
│ protrftypes │ │
│ prosrc │ varchar │
│ probin │ │
│ proconfig │ │
│ proacl │ │
└─────────────────┴───────────────────┘
```
My initial investigation shows that it is safe to pushdown the `INSERT..SELECT` because data is colocated, as long as the larger varchar fits into the smaller one. In the other case, Postgres already errors out: `ERROR: value too long for type character varying(10)`
Contributor guide
Assessment
This issue has not been assessed yet.