Computed fields on related table are not recalculated when returned in mutation data.

[ncrmro]

Describe the bug
I have two tables linked by a foreign key the parent has a computed field, when performing a mutation the parent computed columns return their original values rather than the value post mutation.

I have confirmed the row is updated on refresh web page or when querying the database directly.

To Reproduce
Steps to reproduce the behavior:

CREATE TABLE parent
(
    id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    count int2
);

CREATE TABLE child
(
    id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id uuid REFERENCES parent NOT NULL,
    count     int2
);

CREATE OR REPLACE FUNCTION _count(rec parent)
    RETURNS smallint
    STABLE
    LANGUAGE sql
AS
$$
SELECT SUM(count)
FROM child
WHERE parent_id = rec.id
$$;

query ParentQuery {
  parentCollection {
    edges {
      node {
        id
        count
        childCollection {
          edges {
            node {
              count
            }
          }
        }
      }
    }
  }
}

mutation ChildMutation($id: UUID!, $count: Int!) {
  updatechildCollection(
    filter: { id: { eq: $id } }
    set: { count: $count }
  ) {
    records {
      id
      count
      parent {
        id
        count
      }
    }
  }
}

Expected behavior
I expect the related fields computed column to correctly return an updated value.

Screenshots
If applicable, add screenshots to help explain your problem.

Versions:

• PostgreSQL: public.ecr.aws/supabase/postgres:15.1.0.42-rc2
• pg_graphql commit ref: Not sure but supabase -v is 1.38.7

Additional context
Add any other context about the problem here.

[imor]

Hi @ncrmro,

Computed field functions written in SQL should be marked volatile to avoid stale results from mutations. This is a direct consequence of Postgres volatility category behaviour. Quoting from the docs:

For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the volatility category, namely the visibility of any data changes that have been made by the SQL command that is calling the function. A VOLATILE function will see such changes, a STABLE or IMMUTABLE function will not. This behavior is implemented using the snapshotting behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.

I have updated the docs in #443 with this suggestion.

In other words, to fix stale results, change your function to this:

CREATE OR REPLACE FUNCTION _count(rec parent)
    RETURNS smallint
    VOLATILE
    LANGUAGE sql
AS
$$
SELECT SUM(count)
FROM child
WHERE parent_id = rec.id
$$;