Graphql-engine: Cannot insert more than 1 row simultaneously into table with generated columns

[medvesekg]

Using

• Hasura v1.2.0
• Postgres 12.2
  

  Steps to reproduce

1. Create a fresh Hasura app on Heroku
2. Create a table
   
3. Add generated column to table

ALTER TABLE people 
ADD COLUMN full_name text 
GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED

1. Inserting a single row works fine
   

2. Inserting more than one row gives an error
   

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "statement": "WITH \"people__mutation_result_alias\" AS (INSERT INTO \"public\".\"people\" ( \"first_name\", \"full_name\", \"last_name\", \"id\" ) VALUES (($1)::text, DEFAULT, ($2)::text, DEFAULT), (($3)::text, DEFAULT, ($4)::text, DEFAULT)  RETURNING * , CASE WHEN 'true' THEN NULL ELSE \"hdb_catalog\".\"check_violation\"('insert check constraint failed')  END ), \"people__all_columns_alias\" AS (SELECT  \"id\" , \"first_name\" , \"last_name\" , \"full_name\"  FROM \"people__mutation_result_alias\"      ) SELECT  json_build_object('affected_rows', (SELECT  COUNT(*)  FROM \"people__all_columns_alias\"      ) )        ",
          "prepared": true,
          "error": {
            "exec_status": "FatalError",
            "hint": null,
            "message": "cannot insert into column \"full_name\"",
            "status_code": "42601",
            "description": "Column \"full_name\" is a generated column."
          },
          "arguments": [
            "(Oid 25,Just (\"John\",Binary))",
            "(Oid 25,Just (\"Doe\",Binary))",
            "(Oid 25,Just (\"Jane\",Binary))",
            "(Oid 25,Just (\"Doe\",Binary))"
          ]
        },
        "path": "$.selectionSet.insert_people.args.objects",
        "code": "unexpected"
      },
      "message": "database query error"
    }
  ]
}

[tirumaraiselvan]

Related: https://github.com/hasura/graphql-engine/issues/4506

[codingkarthik]

When an insert mutation is attempted, Hasura adds the default value to all those columns in the table that have not been provided in the mutation.

The issue here is faced due to a bug in postgres itself, when trying to insert rows with the default value for the generated column, it fails. Although a single row insert with a generated column works fine.

So, a workaround maybe to do multiple single-row inserts when there’s a generated column.

The postgres bug is tracked here

[tirumaraiselvan]

Aside from this getting fixed automatically when PG bug is fixed, maybe we can think about not including the columns which were not explicitly sent from the client in the generated insert statement.

[huss-jay]

Any updates on this? Not sure what the solution is to inserting multiple rows with a generated column.