hasura / hasura/graphql-engine
citus: support relationships in a mutation's returning field
- Dominant language
- TypeScript
- Stars
- 32.1k
- Forks
- 3k
- PR merge metrics
- PR metrics pending
Description
Mutations which include relationships in `returning` field currently fail.
```graphql
mutation insert_returning_relationships {
insert_disaster(objects: {name: "cyclone_test" country_id: 1}) {
affected_rows
returning {
affected_states {
state_id
}
}
}
}
```
```yaml
message: "cannot pushdown the subquery",
status_code: "0A000",
description: "Complex subqueries and CTEs cannot be in the outer part of the outer join"
```
We'll need to change how we query for relationships for a mutation's `returning` field. This is the current approach:
```sql
begin;
insert ... returning *;
with table as (select * from VALUES (...))
select * from table join ...
commit;
```
The idea is to treat the affected rows as a table and join it with the relationship tables. The LHS is currently constructed by fetching all the columns of affected rows using `returning *` and converting that to a table expression using `VALUES`.
#### Why do we not treat the update itself as a CTE?
i.e, instead of a transaction, we could've done:
```sql
with table as (insert ... returning *)
select * from table join ...
```
This doesn't work because of how Postgres executes CTE statements - all the statements are executed against the same snapshot, so they cannot "see" the effects of other statements which results in edge cases.
#### Why do we not just select primary key columns in `returning`?
i.e,
```sql
begin;
insert ... returning pkey_columns;
select * from table where id in (list of pkeys) join ...
commit;
```
Postgres supports modifiable views but views do not have primary key columns so we'll need to use `returning *` and construct the table expression using `VALUES`. Instead of having two different codepaths for tables and views, the same approach is used for both views and tables. However this causes issues with Citus as Citus cannot push down the LHS of the join to the worker nodes constructed using `VALUES` expression but it can push down if LHS is of the form:
```sql
select * from table where id in (list of pkeys)
```
So we'll need to switch to using the above form for tables and for views we'll need to ask the users to give the set of columns that can act as an identifier for a view's row (which will be part of Hasura's metadata)
Contributor guide
Research direction
Start by tracing the existing mutation returning relationship query path described in the issue: the transaction, returning * result, VALUES table expression, and relationship join. Compare the table primary-key approach with the view identifier metadata requirement; done means Citus can push down table queries and view relationships have a defined identifier path without the reported error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- graphql, postgres
- Domain
- api, database, distributed-systems
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 25/100