Feature request: some way to disable warning when a nested upsert affects zero rows

[bitjson]

This is a feature request related to https://github.com/hasura/graphql-engine/issues/1911#issuecomment-702992930.

As described in Nested upsert caveats, Hasura currently returns an error if an upsert mutation with nested upserts does not specify any update_columns:

cannot proceed to insert object "parent" relation since insert to table "parent" affects zero rows

In many online analytical processing use cases data can be considered immutable, and if a particular parent object was previously inserted, we can be confident that its child objects were also inserted correctly.

It would be great if there were some way to intentionally disable this warning message. I'd like to be able to tell the server to, "insert this complex object if you don't already have it. If you have it, skip checking the nested objects and just return a successful result."

[dorazouri]

Hi, I could really use this feature also. Is there any workaround to make a nested mutation idempotent? Currently I see 2 options for achieving the desired behavior, both of them hurt another aspect:

1. Including the constraint columns in the update_columns list - this results in an unnecessary update query to the database
2. Performing a pre-query before the mutation and modifying the mutation based on some logic - this results in a surplus operation + extra logic (logic that as I see is already implemented in GraphQL in the first place, on which the on_conflict "callback" is based)

[marcghorayeb]

Seeing this as well... I have to manually silence these errors on the caller side which is a pain unfortunately :(

[seanonthenet]

Bump on this one.

I'm having to catch this specific error and ignore it because I have an empty update_colums array on an on_conflict clause in an insert mutation and if I put any column in there then the nested inserts are performed which is not desirable.

Exactly as @bitjson described: "insert this complex object if you don't already have it. If you have it, skip checking the nested objects and just return a successful result."

Even if this only worked with insert and not insert_one and then affected_rows could return 0. Which would be fully backward compatible.

Also, the error code currently returned as not-supported is very vague. It needs to be at least something like affected-zero-rows to be handled correctly. I am rather checking the message in this case to see if it contains "affects zero rows"

Current error example eg:

{
  "response": {
    "errors": [
      {
        "extensions": {
          "path": "$.selectionSet.insert_order_one.args.object[0]",
          "code": "not-supported"
        },
        "message": "cannot proceed to insert array relations since insert to table \"order\" affects zero rows"
      }
    ]
  }
}

[mcsimps2]

Also running into this problem. I'm doing something like so on a nested insert:

INSERT ... ON CONFLICT DO UPDATE SET active = TRUE, last_updated = NOW() WHERE active = FALSE;

For plenty of cases where active = FALSE, this will return nothing and cause the command to error out.

[Kirill89]

This is also a problem for me. I want to use upsert to put complex objects (spread in several tables) into the database. And I would like to skip the entire operation if the object already exists in the database.