citusdata / citusdata/citus

Citus marks varchars with different lengths as co-located, but doesn't support INSERT..SELECT pushdown

Open
#2,874 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

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

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.