JdbcAggregateOperations delete by query

[micopiira]

While comparing R2dbcEntityOperations and JdbcAggregateOperations I noticed that R2dbcEntityOperations has a method to delete entities by query: R2dbcEntityOperations#delete(org.springframework.data.relational.core.query.Query,java.lang.Class) but JdbcAggregateOperations does not.

I'm proposing to add following method to the JdbcAggregateOperations interface:

void delete(org.springframework.data.relational.core.query.Query query, Class<?> domainType);

[ivamly]

Hi, @schauder!

Could I work on this? If so, is there any additional information I should know before I start working on it?

Thanks!

[schauder]

Sure, go ahead.

[ivamly]

Hi @schauder ! I just wanted to confirm that my understanding of the implementation is correct. Here's what I think needs to be done:

1. The delete method will be executed via DataAccessStrategy, as all methods that use Query are executed in this way.
2. We need to add a method in SqlGenerator to generate the SQL query for the delete operation.
3. A method needs to be added in SqlParametersFactory to retrieve the parameters based on the provided Query.
4. Finally, we will execute the delete operation via operations.update().

Is my understanding correct? Any advice would be greatly appreciated. Thanks!

[schauder]

Sounds good.

[micopiira]

@ivamly Any progress on this one?

[ivamly]

@micopiira Hi! No, I'm sorry, I don't think I'll be able to do it any time soon.

[ghgh415263]

@ivamly Would it be okay if I worked on this issue?

[schauder]

Sure, why not.

[ghgh415263]

Hello! I'm working on this issue and I have questions

1. I'm implementing function. It currently throws an exception ("Delete requires criteria to avoid full-table delete") if the query’s criteria are absent. I'm wondering if there’s a more idiomatic or effective approach to handle that scenario. (or to accept an empty criteria )
2. Is it a good approach to first fetch IDs using a select with query and then use those IDs to perform a deleteAllByIds operation to delete an aggregate and its associated sub-entities? Or would it be better to simply delete the root aggregate only, without deleting the sub-entities?

[schauder]

While probably not often used I don't see anything inherently wrong with deleting with an empty criteria. After all we also have deleteAll in the CrudRepository.

And we absolutely have to delete sub entities with the aggregate root.

The proposed tests seem fine.

[schauder]

I discussed this topic with the team and we decided that we are not going to produce events for deleted aggregates. Therefore we can and should delete them directly without the extra step of first loading the aggregate ids.

[ghgh415263]

So it means I only need to run a single DELETE query on table, and the related sub-entities will remain unaffected?

[schauder]

No, we still delete related entities, just as with deleteById but we don't load the aggregates first.

So if we have an aggregate root A which has a collection of B and want to delete As based on a predicate P we basically do:

1. delete B where b.a_id in (select id from A where P)
2. delete A where P

[ghgh415263]

I feel like my question from two weeks ago was a bit lacking in detail. I'd like to ask a more thorough and well-structured question this time.

I looked into how the delete methods of JdbcAggregateTemplate works (deleteAllByIds, deleteAll ...), and it seems that it first acquires row-level locks before actually performing the delete operation. Based on that, I was thinking of implementing it with the following steps:

1. SELECT id FROM root_table WHERE Query Criteria FOR UPDATE;
2. DELETE FROM sub_entity_table WHERE root_table_id IN (result from 1);
3. DELETE FROM root_table WHERE id IN (result from 1);

(Select only the IDs of the target aggregates with FOR UPDATE to acquire row locks, without fetching the full aggregates. Delete related records from the sub-entity table using those IDs. Delete the root table records using those IDs.)

In your most recent response, you suggested the following approach:

delete B where b.a_id in (select id from A where P) (delete subEntity with subquery) delete A where P (delete root)

I just want to make sure I’m understanding your suggestion properly. Does this mean I don't need to explicitly acquire row-level locks, and I can simply execute these two SQL statements? In my approach, I was planning to use an IN clause for the delete statements based on a pre-fetched list of IDs, but it sounds like you're suggesting I should apply the query criteria directly in the WHERE clause instead. Is that correct?

[schauder]

Locking is a good point. Delete by query should behave just as deleteAllByIds in this regard.

[ghgh415263]

Then I plan to proceed with the following steps.

1. SELECT id FROM root_table WHERE Query Criteria FOR UPDATE;
2. DELETE FROM sub_entity_table WHERE root_table_id IN (result from 1);
3. DELETE FROM root_table WHERE id IN (result from 1);

or

1. SELECT id FROM A WHERE P FOR UPDATE;
2. delete B where b.a_id in (select id from A where P)
3. delete A where P

[micopiira]

I'm wondering if this logic related to deleting relationships is consistent with the R2dbcEntityTemplate delete by query? If not, should the logic be implemented there also?

[schauder]

We don't need the locks for R2DBC, because we don't operate on multiple tables there, so there is no risk of a deadlock, which was the reason for introducing locking with SD JDBC.