hasura / hasura/graphql-engine
Mutation using volatile function and relationship giving stale results
- 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
Assessment
This issue has not been assessed yet.