Mutation using volatile function and relationship giving stale results

[jimzer]

Hello,

First of all, thank you for this amazing tool!

I'm building a mutation with a tracked Postgres volatile function.
I have a blogposts and tags types linked by a many-to-many relationship
Here is the related schema.

type blogposts {
  body: String!
  created_at: timestamptz!
  date: date
  description: String!
  id: Int!
  image: String!
  publish: Boolean!
  slug: String!
  tags(...): [blogpost_tag!]!
  tags_aggregate(...): blogpost_tag_aggregate!
  title: String!
  updated_at: timestamptz!
}

type tags {
  blogpost_tags(...): [blogpost_tag!]!
  blogpost_tags_aggregate(...): blogpost_tag_aggregate!
  created_at: timestamptz!
  id: Int!
  name: String!
  updated_at: timestamptz!
}

Then I have a volatile function to update a blogpost, it deletes the old tag relationships, add the new ones and update the tile field:

CREATE OR REPLACE FUNCTION public.update_blogpost(id integer, tags integer[], title text)
 RETURNS SETOF blogposts
 LANGUAGE sql
AS $function$
    
    DELETE FROM blogpost_tag 
    WHERE blogpost_id=id;
        
    INSERT INTO blogpost_tag
    SELECT id, unnest(tags);
    
    UPDATE blogposts SET title=$3 WHERE blogposts.id=$1;
    
    SELECT * FROM blogposts WHERE blogposts.id=$1;

$function$

Here is the associated GraphQL mutation as I use it:

mutation {
  update_blogpost(args: {id: 1, tags: "{1}", title: "Test"}) {
    id
    title
    tags {
      tag {
        id
        name
      }
    }
  }
}

The problem is the following: when performing the mutation, the relationship results returned are not the last one, they are the ones before the mutation.

For instance, if before the mutation I had only one tag and after the mutation, I have 3 tags, the fields returned by the mutation will only contain one tag.

I suspect that Hasura is joining the result from the function with the many-to-many table, and the Postgres optimizer might run the select for the tag part before the function so I get stale results.

Indeed running this SQL return the same problem:

SELECT id, title, blogpost_id, tag_id FROM update_blogpost(1, array[1,2], ''), blogpost_tag AS bt WHERE bt.blogpost_id=1;

However, decomposing in 2 queries works:

SELECT * FROM update_blogpost(1, array[1], '');
SELECT id, title, blogpost_id, tag_id FROM blogposts, blogpost_tag AS bt WHERE bt.blogpost_id=1 and id=1;

IMO it's a problem because you don't want stale results returned from a mutation.

Maybe I do something wrong, or there is a solution, but I could not find anything.

Thanks for your help.

[0x777]

(internal note for implementation)

If we are generating SQL of this form for volatile functions, it is incorrect:

select * from volatile_function(args) f join rest_of_table_relationships

For non-volatile functions, the above would work, i.e, we can treat f(args) as a table expression but not for volatile functions because of Postgres's snapshot isolation - the data modified by f is not visible to the statement (see https://www.postgresql.org/docs/current/queries-with.html). We work around this in insert/update/delete mutations by doing it a multi-step execution:

begin
modified_rows <- do mutation
response <- modified_rows join relationship_tables
commit

The same approach has to be taken to process a volatile-function's response.

[Bessonov]

Probably, I ran in the same issue. In the core, I expose a volatile function as mutation. Minimal example:

create table users (
  id uuid NOT NULL,
  PRIMARY KEY (id)
);

create table phones (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  userid uuid NOT NULL
    REFERENCES users(id),
  phone text,
  PRIMARY KEY (id)
);

CREATE FUNCTION insert_user_derived(phone text)
RETURNS SETOF users AS $BODY$
  WITH
  InsertedUser AS (
    INSERT INTO users (id) VALUES (gen_random_uuid())
    RETURNING *
  ),
  InsertedPhones AS (
    INSERT INTO phones (userid, phone)
    SELECT id, phone FROM InsertedUser
  )
  SELECT * FROM InsertedUser;
$BODY$ LANGUAGE sql VOLATILE;

Now, hasura generates something like:

-- look at the query plan: it seems like this function isn't executed
explain analyse select *
from insert_user_derived('0000000')
where
	-- usually there is clause of select permission for `users` table,
	-- but to make example more thin, just check if any entry is visible
	exists (select * from users)
	or exists (select * from phones);

Playground: https://www.db-fiddle.com/f/kHANnWCdxMLiTFtJa8dWiK/0

EDIT: because of permission check, the in hasura used function is executed, but it returns no results.

Is there any workaround?

[josesei]

Any updates on this?

[gjacobrobertson]

I am also running into this bug. I am working around it for now by doing a trivial update in a separate field in the same mutation, e.g.

mutation MyMutation($id: uuid) {
  volatile_function(args: { id: $id}) {
    id
  }
  update_thing_by_pk(pk_columns: { id: $id}, _set: {modified_on: "now()"}) {
    id
    relationship_modified_by_volatile_function {
      id
    }
  }

But I would definitely expect the graphql engine to better account for volatile functions and their potential to have side effects.

[josesei]

It would be really great to get this solved, it would prevent us from writing duplicate re-fetchs or hackish workarounds

[zzorba]

Just encountered this bug today for the first time. Would be great to see this fixed someday.