citusdata / citusdata/citus

PG 16 support: Equivalence class changes break some queries

Open
#6,895 5 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

As we plan ahead for PG 16 support, I realized that this commit https://github.com/postgres/postgres/commit/2489d76c4906f4461a364ca8ad7e0751ead8aa0d#diff-60dfe720257ddb41ac40a211ea8eb3ce37196ed01ab9102f37e75a75a66b996fR325-R337

Breaks some `INSERT .. SELECT` queries, such as

```
INSERT INTO agg_results_second(user_id, value_2_agg)
SELECT user_id, value_2 FROM users_table as car WHERE
user_id = 1 AND
NOT EXISTS (SELECT user_id FROM events_table as foo WHERE user_id=car.user_id);

ERROR: cannot perform distributed planning for the given modification
DETAIL: Insert query cannot be executed on all placements for shard 102072
```

Because now, we cannot deduct that user_ids are always equal due to anti join.

Instead, PG16 decides that all user ids are equalivant to constant 1. That breaks our equivalence checks.

This is not a problem for router queries, as they are still concluded as push-down. However, INSERT .. SELECT does not have `router` logic, hence equivalence checks are broken.

Though, it is surpising that we can still pushdown:

```SQL
explain SELECT user_id, value_2 FROM users_table as car WHERE
NOT EXISTS (SELECT user_id FROM events_table as foo WHERE user_id=car.user_id);
```

So, needs to double check what is going on.

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.