hasura / hasura/graphql-engine

citus: support relationships in a mutation's returning field

Open
#7,185 0 comments 4 reactions 0 assignees View on GitHub
k/enhancement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.