Add support for `.delete()` in `Entity`

[ra0x3]

• As mentioned in https://github.com/FuelLabs/fuel-indexer/issues/1501
• This should execute a simple Postgres DELETE query
• Ideally .delete() could be called on a single instance of Entity and ::delete() could be passed a query where in which multiple instances matching the given query are deleted

.delete()

extern crate alloc;
use fuel_indexer_utils::prelude::*;

#[indexer(manifest = "indexer.manifest.yaml")]
mod indexer_mod {
    fn do_a_thing(e: Event) {
       // Using .delete() on a single instance
       let item = Event::find(Event::field().eq(e.field)).unwrap();
       item.delete();
    }
}

::delete()

extern crate alloc;
use fuel_indexer_utils::prelude::*;

#[indexer(manifest = "indexer.manifest.yaml")]
mod indexer_mod {
    fn do_a_thing(e: Event) {
       // Using ::delete() for all items matching a given query
       Event::delete(Event::field().eq(e.field).and(Event::another_field().eq(e.another_field))).unwrap();
    }
}

• So ::delete() ☝🏼 is effectively just ::find() but with a DELETE instead of a SELECT

[deekerno]

@ra0x3, I'm of the opinion that allowing for record deletion inside of the handler code could lead to a couple of unforeseen issues:

• ensuring the user deletes the correct record set -- honestly, not really something we can prevent if they don't understand how SQL parses things, but if we don't provide the tools, then they can't do it
• database index performance after record deletion -- after several deletions, the table page will become fragmented and the database index will still be using the structure of the old data until it's rebuilt; on the other hand, I'm not sure if Postgres auto-rebuilds database indexes over time or if it's only done at the request of a user

[ra0x3]

Thanks @deekerno

• So first off, yes you're correct that DELETE can lead to degraded performance
• However, I do think that's sort've the indexer owner's responsibility to know - we're not trying to protect people from Postgres
  • If someone thinks they might be deleting a record often they probably shouldn't do that for records that they're also searching for often via a @index directive
• If I'm often deleting records that have an index that I'm often using I should know to REINDEX the data on some interval to clean things up

[deekerno]

Heard. I guess that's more data for the "education gap" that we've been discussing. 😅