Call function (e.g. to update a materialized view) before query

[the-sun-will-rise-tomorrow]

Summary

Is it possible to make PostGraphile call a volatile function to update some tables or materialized views before it queries other tables?

Additional context

The data I'm trying to query needs to be filtered depending on query parameters. E.g. all my tables have a version column, and all queries have one maxVersion parameter, which mean that all nodes should be automatically filtered to exclude all rows with version > maxVersion (and then deduplicated by id taking the row with the biggest version).

Initially I implemented it as SETOF-returning functions, but that turned out to be too slow because PostgreSQL can't inline them very well and does not materialize their output, so e.g. I think if the same table is referenced multiple times then it is computed multiple times.

Then I tried to create "cached" versions of these tables holding the result after filtering, and pre-populate the caches as part of the query, i.e. there is a root data { ... } node which maps to a function which populates the cache tables. This is much faster but isn't working very well because the cache is populated in parallel to other parts of the query which read from the same tables, so for call N we get the results for call N-1.

So I think I need a way to create a root data node which calls a volatile function which populates the cache, and that must happen before any SELECT from any cache table starts running... but if there's another way to generally do parameterized filtering of all tables efficiently then that will work too.

[benjie]

This sounds like a complex way of going about it. Have you considered setting app.max_version in pgSettings and then creating views over your tables

create function my_schema.max_version()
returns int
as $$
select nullif(current_setting(‘app.max_version’, true), ‘’)::int;
$$ language sql stable;

create view postgraphile.my_table as
  select distinct on (id) my_table.*
  from my_schema.my_table
  where version <= my_schema.max_version()
  order by version desc;

comment on view postgraphile.my_table is
  E’@primaryKey id\n@foreignKey other_table_id references postgraphile.other_table’;

[the-sun-will-rise-tomorrow]

Thank you, I have not tried that. I've tried it now, and this way is indeed much simpler.

While further researching the performance issues and adapting the schema to these changes, I discovered that I can ask PostgreSQL to materialize my views on the fly by putting the expression into a MATERIALIZED CTE. This solved all of my performance problems!

Also, do I understand correctly that the only way to specify these settings is via HTTP headers or query parameters, and not as part of the GraphQL query or variables? I am a little concerned that this will interact poorly with caching GraphQL clients.

Thanks!

[benjie]

Also, do I understand correctly that the only way to specify these settings is via HTTP headers or query parameters, and not as part of the GraphQL query or variables? I am a little concerned that this will interact poorly with caching GraphQL clients.

Technically anything that the HTTP request contains could be used, which you could poke down into GraphQL and see if a particular directive/variable was passed, but I wouldn't recommend it. If anything, consider using the extensions property on the GraphQL request. I wouldn't though.

I think you're going to have a hard time with caching GraphQL clients with what you propose anyway, since the identifier of each record (__typename + id) will represent a different node in your graph depending on what "max version" you're using. If you want the caching client to work nicely you need to ensure that each version has a unique id (or you need to override how the client determines the unique identifier) and you need to ensure that there's no implicit filtering on these records - the client should indicate the max id to each and every level in the query to ensure its identified correctly. To understand more, consider reading this article:

https://benjie.dev/graphql/ancestors

With what I proposed with pgSettings you would need to reset your normalized cache any time the max version number were to change.