When using Yugabyte with Hasura, update mutation causes "Missing base table ybctid in index write request"

[vnovick]

Using Yugabyte 2.1.0.0 version and running Hasura on top of it to generate GraphQL, there is a problem with GraphQL update mutations.

For example:

I have two tables:

user (
  id SERIAL PRIMARY KEY
  username TEXT UNIQUE
  last_seen timestamp with time zone
  last_typed timestamp with time zone
)

message (
  id SERIAL NOT NULL PRIMARY KEY
  text TEXT NOT NULL
  username TEXT FOREIGN KEY REFERENCES user(username) NOT NULL
  timestamp timestamp with time zone DEFAULT now() NOT NULL
)

When executing the following mutation:

mutation {
    update_user (
      _set: {
        last_typed: "now()"
      }
      where: {
        id: {
          _eq: 3
        }
      }
    ) {
      affected_rows
    }
  }

Hasura compiles mutation to SQL query:

WITH "public_user__mutation_result_alias" AS 
(UPDATE "public"."user" SET "last_typed" = NOW()  
WHERE (('true') AND ((((("public"."user"."id") = (3)) 
  AND ('true')) 
  AND ('true')) 
  AND ('true'))) 
  RETURNING * ) 
SELECT  json_build_object('affected_rows', (SELECT  COUNT(*)  FROM "public_user__mutation_result_alias") )

This results in the following error:

{
    "statement": "WITH \"public_user__mutation_result_alias\" AS (UPDATE \"public\".\"user\" SET \"last_typed\" = NOW()  WHERE (('true') AND (((((\"public\".\"user\".\"id\") = (3)) AND ('true')) AND ('true')) AND ('true'))) RETURNING * ) SELECT  json_build_object('affected_rows', (SELECT  COUNT(*)  FROM \"public_user__mutation_result_alias\") );",
    "prepared": false,
    "error": {
        "exec_status": "FatalError",
        "hint": null,
        "message": "Missing base table ybctid in index write request",
        "status_code": "XX000",
        "description": null
    },
    "arguments": []
}

[m-iancu]

@vnovick Just based on the schemas and query above I was not able to replicate the issue locally.
Do you have any (secondary) indexes on the tables?
You can run \d "user" and \d "message" in ysqlsh to get that info.

[vnovick]

Yeah I do:

                                      Table "public.user"
   Column   |           Type           | Collation | Nullable |             Default
------------+--------------------------+-----------+----------+----------------------------------
 id         | integer                  |           | not null | nextval('user_id_seq'::regclass)
 username   | text                     |           | not null |
 last_seen  | timestamp with time zone |           |          |
 last_typed | timestamp with time zone |           |          |
Indexes:
    "user_pkey" PRIMARY KEY, lsm (id HASH)
    "user_username_key" UNIQUE CONSTRAINT, lsm (username HASH)
Referenced by:
    TABLE "message" CONSTRAINT "message_username_fkey" FOREIGN KEY (username) REFERENCES "user"(username)

                                    Table "public.message"
  Column   |           Type           | Collation | Nullable |               Default
-----------+--------------------------+-----------+----------+-------------------------------------
 id        | integer                  |           | not null | nextval('message_id_seq'::regclass)
 text      | text                     |           | not null |
 username  | text                     |           | not null |
 timestamp | timestamp with time zone |           | not null | now()
Indexes:
    "message_pkey" PRIMARY KEY, lsm (id HASH)
Foreign-key constraints:
    "message_username_fkey" FOREIGN KEY (username) REFERENCES "user"(username)

[ndeodhar]

Thanks for reporting @vnovick . We are able to reproduce it on our end.
@m-iancu It reproduces when the table has a row with user.id=3.

yugabyte=# insert into "user"(username) values('aa');
INSERT 0 1
Time: 10.361 ms
yugabyte=# insert into "user"(username) values('bb');
INSERT 0 1
Time: 5.120 ms
yugabyte=# insert into "user"(username) values('cc');
INSERT 0 1
Time: 3.846 ms
yugabyte=# select * from "user";
 id | username | last_seen | last_typed 
----+----------+-----------+------------
  1 | aa     |           | 
  2 | bb     |           | 
  3 | cc   |           | 
(3 rows)

Time: 3.940 ms
yugabyte=# WITH "public_user__mutation_result_alias" AS 
(UPDATE "public"."user" SET "last_typed" = NOW()
WHERE (('true') AND ((((("public"."user"."id") = (3)) 
AND ('true'))  
AND ('true'))    
AND ('true'))  
RETURNING * ) 
SELECT  json_build_object('affected_rows', (SELECT  COUNT(*)  FROM
"public_user__mutation_result_alias") );                                                                                                                                                                                                                                                                                                                    
ERROR:  Missing base table ybctid in index write request
Time: 3.225 ms

[m-iancu]

Root cause was using update with returning for a table that has indexes (or foreign keys).
Additionally, the SET clause right-hand-side expression had to include a non-immutable (stable or volatile) function (in this example now()) so that it is evaluated during the specific execution step.

Simpler way to replicate:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  a TEXT UNIQUE,
  b timestamp with time zone
);

INSERT INTO test (a,b) values ('foo', NOW());
UPDATE test SET b = NOW() where id = 1 RETURNING *;

ERROR:  Missing base table ybctid in index write request

[m-iancu]

Fixed by f8ca1c2.