URI too long when filtering with big lists

[Shenrak]

Bug report

Describe the bug

Had a headache through this one. Tried to delete entries that no longer exists : Made a list of Guid of [still existing] entities, and used a filter as following :

        // Deletion of no longer exists
        List<object> entityIdList = _careItemDataList.Select(x => (object)x.EntityId.ToString()).ToList();
        QueryFilter exists = new("entity_id", Postgrest.Constants.Operator.In, entityIdList);
        await SupabaseManager.app.From<SchemaCareItemData>().Not(exists).Delete();

But i got :

After further research, turns out the URI is too long : (Helpers file in Supabase Core, line 107)

To Reproduce

Create a filter with a big list (388 Guids as for me)

Expected behavior

Complete properly the request

System information

• OS: Windows

[acupofjose]

Unfortunately this looks like a limitation of postgrest - see here for some more info/possible solutions. (https://github.com/supabase/postgrest-js/issues/393#issuecomment-1404012263)

This isn’t something I can fix presently - an alternative would be to make a direct connection using your Postgres credentials in supabase?

[Shenrak]

Thanks for the fast reply :)

An alternative would be to delete every entry for the user and re-insert everything, but i don't like the idea I'm not familiar with the Supabase ecosystem (yet), but in the issue you linked, they talk about Supabase functions, would you go that way ?

[acupofjose]

Sure! I don't have enough information on the problem your code is trying to solve - but it looks like you've got orphaned data you're trying to clear? So I'm just taking a stab at it.

If it's orphaned data server side, I'd set up a foreign key constraint set to CASCADE DELETE. If it's orphaned client side, I'd just run the delete query on a smaller set of data more actively. (You've got a list of 300+ orphaned client side data? Is it possible to run that query more frequently?)

But yes, RPC is a great resource. Like I said, I don't know your structure, problem, etc. but it is definitely a possible approach!

[Shenrak]

Thanks for your insight, i ended up with something like this :

drop function if exists delete_old_care_item_data(text);
create or replace function delete_old_care_item_data(still_existing text)
returns int as $$
  WITH deleted AS (
    delete from public.care_item_data where entity_id::text not in (SELECT unnest(string_to_array(still_existing, ';'))) RETURNING *
  )
  select count(*) from deleted
$$ language sql;

And C# side :

        // Deletion of no longer exists
        string entityIdList = string.Join(';', _careItemDataList.Select(x => x.EntityId.ToString()));
        await SupabaseManager.app.Rpc("delete_old_care_item_data", new() { { "still_existing", entityIdList } });

        // Insert or Update of still existing
        if (_careItemDataList.Count > 0) { await Where(SupabaseManager.app.From<SchemaCareItemData>()).Upsert(_careItemDataList); }

The game (unity) deletes objects during play-time, and i need to delete them from db even if they're not in the "possessed" list of the player, otherwise the player will retrieve them the next time he loads

It works like a charm ! It runs every 10 seconds (during a total save of the player state) Thanks again

[acupofjose]

Perfect - Super happy to see supabase being used with Unity! Thanks for showing your code. Would love to showcase the result when you've finished!