Deploy preview for hasura-docs ready!

Built with commit 9f0a5d0

https://deploy-preview-3598--hasura-docs.netlify.com

Review app for commit ea0cab9 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-ea0cab92

Review app for commit ef9d1ee deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-ef9d1ee5

Review app for commit 5fe1cce deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-5fe1cce3

Tests are now passing 🎉

@paf31 I looked through the changes, from what I understand, the generated insert statement is of this form:

WITH 
  "<table>__mutation_result_alias" AS ( insert statement returning * ),
  "<table>__inserted" AS ( SELECT .. ),
  "<table>__checks" AS ( SELECT bool_and( .. WHEN check_expression ..) ) 
SELECT
  "<table>__inserted".*
FROM
  "<table>__inserted",
  "<table>__checks"

I experimented with a check condition which involves a condition on another table through a relationship, in such cases the following insert statement has a better plan (check expression is computed in returning):

WITH 
  "<table>__mutation_result_alias" AS 
    ( insert statement returning *, check_expression AS _check_expression_value ),
  "<table>__inserted" AS ( SELECT .. ),
  "<table>__checks" AS ( SELECT bool_and( .. WHEN _check_expression_value ..) ) 
SELECT
  "<table>__inserted".*
FROM
  "<table>__inserted",
  "<table>__checks"

The two statements that I used are as follows:

explain analyze with "address_mutation" as (
  update
    address a
  set
    location = 'blr'
  where
    id = 1 returning a.*
),
"address_check" as (
  select
    bool_and(
      case
        when (
          exists (
            select
              1
            from
              "user" u
            where
              u.id = a.user_id
          )
        ) THEN NULL
        ELSE "hdb_catalog"."check_violation"()
      end
    )
  from
    "address_mutation" a
),
"address_return" as (
  select
    json_agg(a.*) as returning
  from
    "address_mutation" a
)
select
  a.*
from
  "address_return" a,
  "address_check";

explain analyze with "address_mutation" as (
  update
    address a
  set
    location = 'blr'
  where
    id = 1 returning a.*,
    (
      exists (
        select
          1
        from
          "user" u
        where
          u.id = a.user_id
      )
    ) as _check_condition
),
"address_check" as (
  select
    bool_and(
      case
        when (a._check_condition) THEN NULL
        else "hdb_catalog"."check_violation"()
      end
    )
  from
    "address_mutation" a
),
"address_return" as (
  select
    json_agg(a.*) as returning
  from
    "address_mutation" a
)
select
  a.*
from
  "address_return" a,
  "address_check";

Regarding the check_violation function defined in hdb_catalog, it would be best not to rely on functions/views defined in hdb_catalog in the light of #3533. The function body can be inlined in the statement itself?

During this refactor, I was also hoping that we would end up with simplified logic and fix some issues:

Better error messages

It would be good to have the object on which the constraint failed in the returned error message instead of the current "check constraint failed" (maybe something like "the following object '{"location":"blr"}' does not satisfy the check constraint"). This is particularly useful when you are inserting more than one objects or when inserting related data (through relationships).

Different code paths based on relationships in selection set

Assume the following schema:

user:
- id
- name
- addresses (array relationship to address table)
address:
- id
- user_id 
- location 
- user (object relationship to user table)

Let's say we issued a mutation such as this:

mutation insert_address {
  insert_address(objects: {location: "blr"}) {
    affected_rows
    returning {
      user {
        addresses {
          id
          location
      }
    }
  }
}

Now, ideally you would expect to generate SQL such as this:

with "insert_result" AS ( INSERT INTO .. )
select .. from "insert_result"

However, this doesn't work as expected (the address inserted as a part of this mutation will not be returned in the addresses relationship) because of how CTEs behave :

All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables

So, whenever there are relationships in returning's selection set, what we do is something different:

BEGIN
inserted rows <- INSERT INTO ...
WITH (inserted rows) AS "insert_result" SELECT .. from "insert_result"
COMMIT

This also applies in case of deletes and updates. I was wondering if having the same code path would reduce some of the issues that we have been seeing with inserts (#3148, #3375, #3609).

I have to admit, I'm not 100% familiar with the current insert mutations implementation so I do not know the implication of the changes that I'm proposing. @rakeshkky thoughts?

@0x777 I'm not sure how I would inline the function body into the call site since it contains a RAISE statement.

I'm all for consolidating the different code paths. To be honest, I found the current way quite confusing, and I was trying to preserve the existing behavior.

Review app for commit 4c79b84 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-4c79b84a

Review app for commit acf0ce5 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-acf0ce5c

Review app for commit dd2cac2 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-dd2cac29

Review app for commit 52f1070 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-52f10702

Review app for commit b563972 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-b5639727

Review app for commit 6c4768b deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-6c4768b5

Review app for commit 9a8af83 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-9a8af83e

Review app for commit e825e64 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-e825e641

Review app for commit 9f0a5d0 deployed to Heroku: https://hge-ci-pull-3598.herokuapp.com
Docker image for server: hasura/graphql-engine:pull3598-9f0a5d07

Review app https://hge-ci-pull-3598.herokuapp.com is deleted