Pruning logs through the API on a schedule

[ankushranka]

This link provides information on how to prune eventing logs https://hasura.io/docs/latest/graphql/core/event-triggers/clean-up/

One way to do this on a schedule is to directly interact with the underlying database and run the SQL.

Is there a way to have a mutation that can be called through the GraphQL or run raw SQL directly through the API?

[meetzaveri]

Hey @ankushranka

Current Scenario (using raw SQL )

As you can see here - https://hasura.io/docs/latest/event-triggers/clean-up/, we have defined options on how to prune event trigger data via running SQL commands.

Possible solution via graphql API

For the request you are making, we can achieve that by writing custom SQL functions and exposing them over GraphQL API. Hasura GraphQL engine lets you expose certain types of custom functions as top level fields in the GraphQL API to allow querying them with either queries or subscriptions, or for VOLATILE functions as mutations. Check the docs here - https://hasura.io/docs/latest/schema/postgres/custom-functions/

1. Create SQL function

For this function, you’ll need to create an empty table so that we can use it as return type of function.



Let’s say I create a function as

CREATE OR REPLACE FUNCTION public.prun_event_logs()
 RETURNS SETOF temp_event_log
 LANGUAGE plpgsql
AS $function$
    
    BEGIN
    DELETE FROM hdb_catalog.event_invocation_logs;

    DELETE FROM hdb_catalog.event_log
        WHERE delivered = true OR error = true;
    END;
$function$

This function will execute that SQL defined in it. This SQL code is being used from the examples mentioned in that cleanup docs. Here I have created a temp table called temp_event_log which is empty for this purpose

Also, this function will be created under public schema, and you can check it by verifying on console.

2. Grab that mutation from introspection schema and run it whenever you want


Now head to API tab and select mutation from API explorer. As a result of introspection, you’ll see that there’s mutation called prun_event_logs. This will execute custom defined SQL code into that function. (see the attached screenshot here)




I hope this helps. Thanks !

[ankushranka]

@meetzaveri If i got this correct, the trick is to create an empty table so it can be used as a return for the custom SQL. Works. Thank you so much.

[meetzaveri]

the trick is to create an empty table so it can be used as a return for the custom SQL

yes @ankushranka , that's correct

[tirumaraiselvan]

On Cloud/Enterprise, you can use the Auto-cleanup feature to do this as well: https://hasura.io/docs/latest/event-triggers/clean-up/auto-cleanup/