Cloud spanner supports cascade deletion on foreign key

[shuto-facengineer]

This release might affect the behavior of spanner-truncate.
See Release Notes, July 18, 2023

[shuto-facengineer]

It is necessary to have schema information that easily facilitates the implementation of the following two logics:

• Identifying tables that would be subject to cascading deletions triggered by the deletion of a specific table.
• Identifying tables that can potentially delete a specific table through cascading deletions.

We need to consider the following aspects to address cascade deletions by foreign key referencing:

• Tables representing hierarchies, such as departments, may sometimes contain references to themselves.
• When reference relationships between tables indirectly form cycles, a tree structure cannot adequately represent this.

Parent-child relationships defined by Interleave can be managed with a tree structure, whereas relationships defined by foreign keys may require a more complex approach, potentially necessitating the graph structure.

It may be useful as a temporary measure to explicitly state that cascade deletion by foreign key has not yet been addressed and to alert users.

[yfuruyama]

If foreign key constraints form the circular dependency, the current way to delete the rows using partition update with DELETE FROM statement won't work, so we need to reconsider how to delete them:

spanner-truncate/truncate/deleter.go

Lines 55 to 56 in 5862e7d

	stmt := spanner.NewStatement(fmt.Sprintf("DELETE FROM `%s` WHERE true", d.tableName))
	_, err := d.client.PartitionedUpdate(ctx, stmt)

But considering that we haven't heard of any feedback for supporting such situation, I think for now it's ok to leave it as it is. It might be too complex to fully support the foreign key constraints.

[shuto-facengineer]

How about adding a description to limitations on Readme like this?

• This tool does not support cascading deletes for foreign keys. If you have any tables which have a foreign key constraints on delete cascade, you need to exclude the referees tables and truncate them yourself later.

[yfuruyama]

This tool does not support cascading deletes for foreign keys. If you have any tables which have a foreign key constraints on delete cascade, you need to exclude the referees tables and truncate them yourself later.

Let's say a basic scenario. If user wants to delete the table1 and table2 where table2 references table1 (table2 -> table1), user can specify the both tables (or implicitly all tables) to delete the both tables.

There is a following line to start deleting table1 first, so the deletion on both tables should work.

spanner-truncate/truncate/coordinator.go

Lines 54 to 58 in 5862e7d

	for _, referencing := range t.referencedBy {
	if referencing.deleter.status != statusCompleted {
	return false
	}
	}

What we would be able to say is that not all scenarios using foreign keys are supported (e.g. circular dependency) despite of usage of cascade deletion.

So I agree that we can add these limitations in the following section: https://github.com/cloudspannerecosystem/spanner-truncate/blob/main/README.md#limitations

[shuto-facengineer]

There is a following line to start deleting table1 first, so the deletion on both tables should work.

Oh, I had completely missed that process.
I will open a PR to add the limitation later.