hasura / hasura/graphql-engine

Mutation using volatile function and relationship giving stale results

Open
#6,865 6 comments 6 reactions 1 assignee Claimed by @0x777 View on GitHub
a/api/graphql a/data/postgres c/server k/bug p/high
Dominant language
TypeScript
Stars
32.1k
Forks
3k
PR merge metrics
PR metrics pending

Description

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.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.