Version Information

Server Version: 2.19.0
CLI Version (for CLI related issue): 2.18.0

Environment

OSS

What is the current behaviour?

When a 3-layer nested insert is executed and the middle layer its primary key column is also the foreign key to the top layer, an error occurs, preventing the insert mutation.

What is the expected behaviour?

When a 3-layer nested insert is executed and the middle layer its primary key is also the foreign key to the top layer, the insert succeeds and all three tables each have a new record added.

How to reproduce the issue?

1. Create a table A0_TABLE with a single column A0_PK, type uuid, not nullable, default value gen_random_uuid(), primary key
2. Create a table A1_TABLE with a single column A1_PK, type uuid, not nullable, no default value, primary key, foreign key to A0_PK (RESTRICT,RESTRICT)
3. Create a table A2_TABLE with a single column A2_PK, type uuid, not nullable, no default value, primary key, foreign key to A1_PK (RESTRICT,RESTRICT)
4. Add a relationship A0_REL_TO_A1 in A0_TABLE, type array, to remote table A1_TABLE, from A0_PK o A1_PK
5. Add a relationship A1_REL_TO_A2 in A1_TABLE, type object, to remote table A2_TABLE, from A1_PK to A2_PK
6. Execute the following insert mutation:

mutation MyMutation {
  insert_A0_TABLE(objects: {A0_REL_TO_A1: {data: {A1_REL_TO_A2: {data: {}}}}}) {
    affected_rows
  }
}

7. The following error occurs:

{
  "errors": [
    {
      "extensions": {
        "code": "validation-failed",
        "path": "$.selectionSet.insert_A0_TABLE.args.objects[0].A0_REL_TO_A1.data[0].A1_REL_TO_A2"
      },
      "message": "cannot insert object relationship \"A1_REL_TO_A2\" as \"A1_PK\" column values are already determined"
    }
  ]
}

Screenshots or Screencast

Please provide any traces or logs that could help here.

2023-02-21 14:42:50 {"detail":{"generated_sql":null,"kind":"database","query":{"operationName":"MyMutation","query":"mutation MyMutation {\n  insert_A0_TABLE(objects: {A0_REL_TO_A1: {data: {A1_REL_TO_A2: {data: {}}}}}) {\n    affected_rows\n  }\n}\n"},"request_id":"0635b3d0-43c0-4899-addf-68656358f8e1"},"level":"info","timestamp":"2023-02-21T13:42:50.064+0000","type":"query-log"}
2023-02-21 14:42:51 {"detail":{"http_info":{"content_encoding":null,"http_version":"HTTP/1.1","ip":"172.19.0.1","method":"POST","status":200,"url":"/v1/graphql"},"operation":{"error":{"code":"validation-failed","error":"cannot insert object relationship \"A1_REL_TO_A2\" as \"A1_PK\" column values are already determined","path":"$.selectionSet.insert_A0_TABLE.args.objects[0].A0_REL_TO_A1.data[0].A1_REL_TO_A2"},"query":{"operationName":"MyMutation","query":"mutation MyMutation {\n  insert_A0_TABLE(objects: {A0_REL_TO_A1: {data: {A1_REL_TO_A2: {data: {}}}}}) {\n    affected_rows\n  }\n}\n","variables":null},"request_id":"0635b3d0-43c0-4899-addf-68656358f8e1","request_mode":"error","response_size":229,"uncompressed_response_size":229,"user_vars":{"x-hasura-role":"admin"}},"request_id":"0635b3d0-43c0-4899-addf-68656358f8e1"},"level":"error","timestamp":"2023-02-21T13:42:50.064+0000","type":"http-log"}

Any possible solutions/workarounds you're aware of?

It's not possible to fix this by changing the generated metadata files of public_A0-TABLE.yaml and/or public_A1-TABLE.yaml such that the insertion order setting would somehow cause Hasura to allow the insert.

As far as we can tell, it does not matter whether the Hasura GraphQL relationship between A0 and A1 defined above are of type array or object, the result is the same. It also makes no difference what value the database connection setting Isolation Level is set to.

We have a complex setup where in our case that's like this scenario, though with obviously more columns in each table but nothing that complicates the scenario (i.e. no other keys or relationships). In our case, there are several tables at the A2 layer that all have a foreign key to A1, but for the sake of submitting this bug we've reduced the scenario as much as possible, to this simple set of steps that causes the error.

We need this setup of tables and keys this way, to make proper use of the the code generation tool that creates TypeScript types based on the database structure and defined GraphQL relationships.

Keywords

nested, insert, column, values, already, determined, primary, foreign, key, cannot, insert, object, relationship,